AVOIDING SORT TRANSFORMATION IN SSIS

If we are using “Merge or Merge Join” transformation, we should pass sorted datasets as a source to the transformation. If we use sort transformation performance will degrade.

            We can avoid the sort transformation by using source “Advance editor” option, if source is excel or OLEDB.

Example:

• Edit the source (excel or OLEDB source) and change the “Data access mode:” to “SQL Command”.

• Writ the select statement for source table with order by (on required fields).

• Click “OK”.
• Right click on the source and select “Show Advanced Editor” option.

• “Advanced Editor for OLEDB Source” window will open. Click on “Input and Output Properties” option.

• In the “Inputs and Outputs:” section, select the “OLEDB Source Output”.

• Right side of the window “Common Properties” will enable.

• Set “IsSorted” property as true.

• In “Inputs and outputs:” section expand the “OLEDB Source Output” list.

• Expand the “Output Columns” list. And select ID column because we need to sort the source data based on ID column.

• ID column “common Properties” list set the “SortKeyPosition” as “1”.

• Click “OK” button to complete the sorting.

OLEDB DESTINATION

            OLEDB destination is required to store the final result set into data base objects.

• From data flow items, drag and drop the “OLEDB Destination” into designer surface.

• Connect the source/transformation green arrow to the “OLEDB Destination”.

• For “OLEDB Destination” connection manager required to access the “data base objects”. So create a new connection manager (If connection manager not created).
• Right click on the “OLEDB Destination” and select “Edit” option. Then “OLEDB Destination Editor” window will open.

• Select the “OLEDB connection manager and Name of the table” from the drop down list.

• If target table is new table that we need to create, then click the "New" button. Find the highlighted part in the below screen shot for your reference.

• “Create table” window will open, with create table script. Modify the table name.

• Press “OK” button. Then “Create Table” window will get disappears.
• Table creation is finished.
• Click on the “Mappings” option in “OLEDB Destination Editor” window.

• Verify the mappings.

• Then press “OK” button. OLEDB Target mappings are finished, execute the package.

OLEDB CONNECTION MANAGER

            OLEDB connection manager is required to access the data base object from the data base servers.

• Open the BIDS. Right click on the “Connection managers” and select “New OLEDB Connection”, find the below screen shot for reference.

• “Configure OLEDB Connection Manager” Window will open. Then click on the “New” button.

• “Connection Manager” window will open.

• Select the “provider” from the dropdown list. Give “Server name, Authentication and Data base”

• Press “Test Connection” to ensure that there are no connection errors. Press “OK”, then the “Connection Manager” window will disappear.
• Press “OK” button in the “Configure OLEDB Connection Manager” Window.

• “Configure OLEDB Connection Manager” Window will disappear.
• Connection manager creation is finished.

• If we need to modify the connection, right click on the connection (highlighted in the above screen shot) and select edit option.

BUSINESS INTEELLIGENCE DEVELOPMENT STUDIO

• Click on start button and then click on “All Programs”

• Click on the “Microsoft SQL Server 2008 R2”, find the below screen shot for reference.

• Under the “Microsoft SQL Server 2008 R2”, select “SQL Server Business Intelligence Development Studo”.

• Then BIDS (Business Intelligence Development Studio) will open. Click on the “Create project option” to create new project.

• New project window will open.

• Choose the template as “Integration Services Project”. Give the required “Integration Services Project” name and “location” to store the project.

• Click “OK” button, then “Sample project-Microsoft Visual Studio” window will open.

•  we can start the SSIS package creation under the solution.

OLEDB SOURCE

• From Data flow sources items drag and drop the "OLEDB source" into designer surface shown below.

•  OLEDB source is required "Connection manager" to access the data base objects.
• Create new "OLEDB connection manager".
• Right click on “OLEDB Source” and select edit option then “OLEDB Source Editor” window will open.

• Specify the Connection manager , Data access made and Table name from drop down list in “OLEDB Source Editor” window, shown below.

• After that press “OK” Button. 

 

• OLEDB source mappings are finished.

SPLIT SOURCE DATA INTO TWO FILES

Requirement: If you want package creation requirement click here

 

Package creation:

• In control flow drags and drop “Data flow task” into designer surface and then edit.
• In data flow drag and drop the “Flat file source” into designer surface and map the source file( Flat file source mapping click here).
• Drag and drop the “conditional split” transformation into designer surface.
• Connect the “Flat file source” green arrow to the “Conditional split”.
• Right click on the “Conditional split” and select edit option. Then “Conditional split transformation editor” window will open. 

• We need to write our conditions in “Condition” box.
• Condition to extract the record name ending with “e”
• RIGHT(Name,1) == "e"
• Condition to extract the record name ending with “k”
• RIGHT(Name,1) == "k"

• Click “OK” button.
• Drag and drop two “Flat File Destination”.

• Select the “Conditional Split”, connect the conditional split green arrow to flat file destinations. 

• Edit the “Flat File Destinations” and map the target files (For mapping click here).

• Execute the package.

Target files output:

Target1:

Target2:

FLAT FILE DESTINATION

            After source and transformation mappings we need to specify the destination to store the result set.

Destination mapping:

• In data flow, after source mapings, drag and drop the “Flat File Destination” into designer surface.

• Select the “Source”, Click on “Source green arrow” and connect to the “Destination” (After source green arow selection click on the “Destination”).

• “Destination” red symble will go if we map target file.
• Right click on the “Destination” and select “Edit” Option.

• “Flat File Destination Editor” window will open.

• In the above screen shot “Flat File Connection Manager” taken existing source connection. We should create new connection for target.
• Click on the “New” button then “Flat File Format” window will open.

• Choose the required format and click “OK” button. “Flat File Connection Manager Editor” window will open.

• We need to give connection manager name (if we don’t give, it will take defoult name).
• Click on browse button and choose the target location then give the name for the target file.

• Click on “Columns” option and check the columns.

• Click “OK” button then ”Flat File Connection Manager Editor” will disapper.
• In “Flat File Destination Editor” window select the “Mappings” option and check the source to target mappings.

• Click “OK” button to complete the destination mappings.

• We can change target file path by editing the connection manager “Destination”.