SSIS

• Introduction
• Data Flow Items
• Data Flow Source
• Flat File Source
• OLE DB Source
• Excel Source
• ADO NET Source
• XML Source
• Raw File Source
•  Data Flow Transformations
• Aggregate
• Audit
• Character Map
• Conditional Split
• Data Conversion
• Derived Column
• Fuzzy Grouping
• Fuzzy Lookup
• Lookup
• Merge
• Merge Join
• Multicast
• OLE DB Command
• Row Count
• Slowly Changing Dimension
• Sort
• Union All
• Pivot
• Unpivot 
• Data Flow Destination
• Flat File Destination
• OLE DB Destination
• Excel Destination
• ADO NET Destination
• Raw File Destination
• Control Flow Items
• Tasks
• Data Flow Task
• Execute Package Task
• FTP Task
• File System Task
• Bulk Insert Task
• Execute Process Task
• Execute SQL Task
• Send Mail Task
• Script Task 

• Containers:
• Foreach Loop Container
• For Loop Container
• Error Handling
  
  • Control flow level error handling:
• Loging or Log file creation
• Event Handlers
• Check points
• Precedence Constraints

•  Data flow level error handling:

• Error output or Redirect row

•  Security
• Config file creation
• Setting Production level 
• Package Deployment
• File System Deployment
• SQL Server Deployment

• Scheduling

• Best Practice Methods
•  Avoid Sort Transformation

• SSIS Scenarios
• Get Source File path from a SQL Table and Process the file
• Writing Expression for Variables
• Create a folder with current date
• Run a SSIS Package Using Batch File

SSIS: Unzip a Password Protected zip file

In SSIS we don't have a task to Unzip the zipped file,  so we need to find a way to achieve this. In this post i am going to use "WinRAR" to Unzip the zipped file which is password protected.

Note: Make sure WinRAR is installed

• In control flow drag and drop "Execute Process Task" from tool box.
• Edit the task and enter the "Executable, Arguments and WorkingDirectory" details (follow the below sample code)

Zip multiple files:

Executable: 

WinRAR.exe file path (where the WinRAR is installed). 

Default path "C:\Program Files\WinRAR\WinRAR.exe"

Arguments:
e -o+ -pTest123 "D:\Kiran\SSIS\Sample\Kiran.rar"

e -o+ -pTest123                               : e<Space>-o+<Space>-p<Password>

"D:\Kiran\SSIS\Sample\Kiran.rar" : "<Zip file path with extension>"

WorkingDirectory:

Give the Target file path (the unzipped files will store in this location)

Sample Path: D:\Kiran\SSIS\Sample\

See Also:
Zip the file/files with password protection

SSIS: Zip the files with Password Protection

In SSIS we don't have a task to zip the file/files,  so we need to find a way to achieve this. In this post i am going to use "WinRAR" to zip the file/files with password protection.

Note: Make sure WinRAR is installed

• In control flow drag and drop "Execute Process Task" from tool box.
• Edit the task and enter the "Executable, Arguments and WorkingDirectory" details (follow the below sample code)

Zip multiple files:

Executable: 

WinRAR.exe file path (where the WinRAR is installed). 

Default path "C:\Program Files\WinRAR\WinRAR.exe"

Arguments:
-pTest123 A Kiran  *.txt

-pTest123   : -p<Password>

A Kiran      : A<Space><Desired zip file name>

*.txt            : <Star (*) represents any file name><Dot(.)><After dot(.) txt represents file type>

WorkingDirectory:

Give the source file path (the files which we are going to zip)

Sample Path: D:\Kiran\SSIS\Sample\

Zip single files:

Follow the above steps and change the Arguments as mentioned below.

-pTest123 A Kiran  TestFile.txt

-pTest123   : -p<Password>

A Kiran      : A<Space><Desired zip file name>

*.txt            : <FileName><Dot(.)><Extension>

See Alos:
Unzip a Password Protected Zip File

Slowly Changing Dimension (SCD)

        The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. For example, you can use this transformation to configure the transformation outputs that insert and update records in the Dimension table.

Example:
      I am having a Customer dimension table and source file. Using source file i need to update the dimension table using SCD Transformation.

Source File:

Target Table Script:

CREATE TABLE DIM_CUSTOMER

(

      ID VARCHAR(10)

      ,NAME VARCHAR(50)

      ,MOBBILE_NUMBER VARCHAR(20)

      ,ADDRESS VARCHAR(1000)

      ,LATEST_FLG VARCHAR(10)

)

        In the above file, Column ID and Name are Fixed attributes (Never change), Mobile number is changing attribute (Can change, we need to maintain latest number) and Address is Historical attribute (Can change but we need to maintain history).

• Open BIDS.
• From Control flow items, drag and drop “Data flow task” into designer surface.

• From toolbox drag and drop "Flat file source" and configure with source file.
• From the toolbox drag and drop "Slowly Changing Dimension" (SCD).
• Connect Flat file source green arrow to SCD.

• Edit the SCD Transformation (select SCD and right click on that and select edit option). 'Slowly Changing Dimension Wizard' will open.

• Click on "Next" button. Create connection manager and Select the required table.

•  Using 'Input Column' drop down list select the required columns. Using 'Key Type' drop down list select the key type.

• Click on 'Next Button'.
• Select 'Dimension Columns' and 'Change Type' from drop down list. Click on 'Next' button.

• Again Click on "Next" button.

• Select Latest_Flg from, Value When Current and Expiration value from the drop down list. Click on "Next" button.
• Again Click on "Next" button. Finally click on "Finish" Button. Then Package will create automatic.

•  Package is ready to execute. Run the package.

Output:

• If we change the Mobile number in the source file, then the change should reflect in the table.

Source File:

• Run the SSIS package.

Output:

• Record is updated as expected.
• If we change Address in the source file, then new record should insert. Existing record Latest flag should update as 'Expired'.

• After source file change, run the SSIS package.

Output:

• Table is updated as expected.

Unpivot Transformation

        Unpivot is the opposite to Pivot transformation. Using Unpivot transformation we can interchange the columns into rows.

Example:
Source table:

CREATE TABLE UNPIVOT_SOURCE

(

YEARS INT,

SOAP NUMERIC(12,4),

BRUSH NUMERIC(12,4),

PASTE NUMERIC(12,4)

)

INSERT INTO UNPIVOT_SOURCE VALUES

(2010,3500.25,3201.45,2100.61)

,(2011,2842.15,3654.75,2236.24)

,(2012,2689.26,3214.23,2432.55)

 

Target table:

CREATE TABLE UNPIVOT_TARGET

(

YEARS INT,

PRODUCT NVARCHAR(30),

TOTAL_PRICE NUMERIC(12,4) 

)

SSIS Package:

• Connect to the SQL Server and create the above tables.
• Open SSIS Package, In 'Control Flow' drag and drop 'Data flow task' from Toolbox.
• Go to Data flow and drag and drop 'OLE DB Source' and configure with source table.
• From Toolbox drag and drop 'Unpivot Transformation', and connect 'OLE DB Source' green arrow.

•  Edit the 'Unpiot transformation'. Select the columns check box, which we are going to convert as rows (Soap, Brush and Paste).

• In the above screen we can see the 'Input Column', 'Destination Column' and 'Pivot key value'. In the 'Destination Column' section we need to give destination column name(TOTAL_PRICE). The source columns 'Soap, Brush and Paste are having total cost information, this information is related to 'TOTAL_PRICE' (destination column).
• In the bottom of the above screen we can see the 'Pivot key value column name' text box. There we need to give the 'Name' for the new Unpivot column (PRODUCT).

• Click on 'OK' button.
• Drag and drop 'OLE DB Destination' and configure.
• Run the ETL.

O/P:

Pivot Transformation

        Using Pivot transformation we can interchange the rows into columns.

Example:
Source table:

CREATE TABLE PIVOT_SRC

(

YEARS INT,

PRODUCT VARCHAR(30),

TOTAL_PRICE NUMERIC(12,4)

)

INSERT INTO PIVOT_SRC VALUES

(2010,'SOAP',3500.25)

,(2010,'PASTE',2100.61)

,(2010,'BRESH',3201.45)

,(2011,'SOAP',2842.15)

,(2011,'PASTE',2236.24)

,(2011,'BRESH',3654.75)

,(2012,'SOAP',2689.26)

,(2012,'PASTE',2432.55)

,(2012,'BRESH',3214.23)

Target table:

CREATE TABLE PIVOT_TARGET

(

YEARS INT,

SOAP NUMERIC(12,4),

BRESH NUMERIC(12,4),

PASTE NUMERIC(12,4)

)

SSIS Package:

• Connect to the SQL Server and create the above tables.
• Open SSIS Package, In 'Control Flow' drag and drop 'Data flow task' from Toolbox.
• Go to Data flow and drag and drop 'OLE DB Source' and configure with source table.
• From Toolbox drag and drop 'Pivot Transformation', and connect 'OLE DB Source' green arrow.

• Edit the 'Pivot Transformation'.
• Go to 'Input Columns', Select required 'Input Columns' check boxes.

• Go to 'Input and Output Properties'
• Expand the 'Pivot Default Input' and 'Pivot Default Output', Using '+' sign.

• In the above screen shot we can see the input columns YEARS,PRODUCT AND TOTAL_PRICE. For these columns we need to set the 'PivotUsage' property value based on column usage.

Pivot Usage key	Description	Sample Source columns
0	The column will passed through unaffected
1	The column values become the rows of the pivot	YEARS
2	The column values become the column names of the pivot	PRODUCT
3	The column values that are pivoted in the pivot	TOTAL_PRICE

 

• Based on Source column usage, i have assigned 'PivotUsage' values. (Years-1,Product-2 and Total_Price-3)

• Click on 'Output Columns' and add New columns using 'Add Column' Button. Rename the New columns.

• After creating output columns, We need to assign 'PivotKeyValue and SourceClumn' Property values for each 'Pivot Default Output' columns. Then only mapping will create from input columns to the output columns.
• 'YEARS' output column values should come from 'YEARS' input column. So We need to assign the input column 'LineageID' values to the output column "SourceClumn property", and output column name to the output column 'PivoteKeyValue'.

• Same way we need to map remaining columns.

Output Column name	Mapping Input column
Years	PioveteKeyValue: Name of the Output Column (Years) SourceColumn: LinageID of the 'YEARS' column
Soap	PioveteKeyValue: Name of the Output Column (Soap) SourceColumn: LinageID of the 'TOTAL_PRICE' column
Paste	PioveteKeyValue: Name of the Output Column (Paste) SourceColumn: LinageID of the 'TOTAL_PRICE' column
Brush	PioveteKeyValue: Name of the Output Column (Brush) SourceColumn: LinageID of the 'TOTAL_PRICE' column

• Click on 'OK' Button.
• Drag and drop 'OLE DB Destination' and configure.
• Run the ETL.

O/P: