With SSIS, most of the time we need to process all the files from the folder. Once file has been processed, we need to move the file to archive folder, so we should know that file has been processed and we have the file in archive folder.

Here, We are going to process the files and then will move the file from source folder to archive folder by appending date and time to the filename, so we can use it for future reference. SSIS will do both of these things, Rename a file and move a file, with File System Task with operation “Rename file”. Let’s review how it works:1.Add Variables:To configure Source folder and Archive Folder by variable

Name

Scope

Data Type

Value

FileName

[Package Scope]

string

SourceFolder

[Package Scope]

string

[Source Folder Path] (example :- c:\Source_Folder)

SourcePath

[Package Scope]

string

TargetFolder

[Package Scope]

string

[Target Folder Path] (example :- c:\Archive_Folder)

ArchivePath

[Package Scope]

string

2. Set variable’s properties expression:Go to Variable->properties and set EvaluateAsExpression and Expression, so we will have Source Path and Archive Path.

Here, we are adding Current Date time to the filename by expression as mentioned below.

3. Add For each Loop Container and set properties :Now, lets loop thru the folder and process each file from the folder by “For Each loop”. Here, we can setup the folder by Expressions->Directory use Expression @[User::SourceFolder]. We should also specify which type of files we are going to process, like “txt”, “csv” etc..by specifying the same in “Files”. We are fetching the file name with extension so that option needs to be selected as displayed in the following screenshot.We need to assign each file name to the variable, by Variable Mappings->set variable [User::FileName] and Index as 0

4. Add File System Task:Add “File System Task” inside “For each Loop Container”5. Set properties for File System Task:This is the place where we need to setup the operation, which will do our job.A. Configure SourceVariableB. Configure DestinationVariableC. Select operation: “Rename File”, which will rename the file and move it to the Archive Folder as we have specified in variable.

6. Run package and check

With SSIS, it is much simple to process multiple files as mentioned above.

Conditional Split transform use split the source row in easy to multiple groups in data flow and which Destination table populated. Lets review the same by reading a Sample text file and separate out the data in two groups.

August 6, 2014

Recently, I was assigned a job to create many DTSX packages. When I review the packages, I found those packages (Information flow) are moreover same. There is just a difference of Source file connection and destination SQL table which is different in each of the package. It might take couple of hours to create each of the package, but I wanted to get it done in few minutes. To achieve the same, I have looked up the DTSX code (XML) and updated as follows to achieve get it done efficiently and save some time.

Please find following steps to achieve the same:

1. Existing package:

2. Copy Package:Right click on existing package and click on copy option and after right click on SSIS Packages folder and Paste on the location

3.Rename Package Name for newly Pasted file:To rename package name as your mind and When message box open then click yes button

4. Open package in Notepad Editor:

Go to folder where package is exist and open with package in notepad editor

5. Replace package Name in Notepad file:

Replace old package name to new package name with Replace option

6. Replace more text

Replace more text if you know to Change (Example: “Activity” text replace to “Job”)

7. Check Replace name

Check all task name, SQL task and Data Flow task in replace with new text and Annotation text will no be changed, so it needs to be change manually

8. Need to change manually

If SSIS package has Package SQL server Configuration then we need to change that manually too.

June 14, 2014

Database Administrators might face this issue while certificate gets expired and database mirroring (Non domain database mirroring) gets disconnected as two servers, Primary and Secondary cannot communicate with each other. The error message can be found in Log as follows:

MessageDatabase Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'. [CLIENT: xxx.xxx.xxx.xxx]

In this post, We are going to review step by step process to resolve this issue by providing renew parameters with certificate configurations.

April 17, 2014

Now, let’s have a look at functionality of Merge Join Transformation task in SSIS.

Benefit of using Merge join is, input datasets can be combination of any two datasets from (Excel file, XML file, OLEDB table, Flat file).Output can be result of INNER, LEFT Outer, or FULL Outer Join on both the datasets.

Merge Join Transformation has two inputs and one output. It does not support an error output.

Use of Merge Join Transformation:

Merge Join is a two-step process. First step is to sort both the input datasets(tables) in the same order, and the second step is apply merge join on the common key.Here rows from both the sorted inputs get matched together.

To Understand Merge Join Transformation in better way, lets take an example with various configuration parameters in SSIS.

1. Create sample tables:

Now we will create input tables named “Department” and “Employee” in Test database.

Select and drag “Data Flow Task”, from “Control Flow Items” to designer surface. Then double click it and Create a New OLEDB connection.

3. Select Input Data Sources:

Select two different Data Sources which you need to perform merge join on as “OLE_SRC_Employee” and “OLE_SRC_Department”. Create a new “OLEDB Connection” to map it to the source datasets.

4. OLEDB Source Editor:

Now double click on “OLEDB Source”, it will open “OLEDB Source Editor” in that provide table configuration parameters and columns mapping from “Columns” tab.

5. Data Sorting:

As the Merge Join Transformation accepts the sorted data as input, we will add the sort transformation in the flow. If you know that the data is already sorted then you can set “isSorted” Property as “True” in the “Advanced Editor” for OLEDB Source of the respective dataset. Or else you can use the Sort Transformation task from “Data Flow” Transformation.

Now we need to add two Sort components and join the green arrow pipeline from “Employee” to one of the sort transformation and other pipeline from “Department” to the other Sort Transformation.

6. Sort Transformation Editor Source 1:

In order to get sorted data, Double click on the “Sort Transformation” that we have connected to “Employee” Dataset to provide the key on which you want to perform sort so that data gets re-ordered in sorted form based on the keys provided. Provide the Sort type as well as sort order if there are multiple keys on which Sort operation will work.

7. Sort Transformation Editor Source 2:

Now we have “Employee” table data in sorted form, in the same way need to configure the sort transformation for Source 2 “Department”.

For the same double click on the “Sort Transformation” which is connected to “Department” dataset, to provide the Sort key and order in which you want to perform the sort in “Sort Type” property in Editor. Please keep in mind that the Sort type for both the source needs to be of the same type. i.e. any one of ascending or descending order.

8. Merge Join Task Component:

Now we will add Merge Join Transformation, so that we can join both the sources together.Drag the pipeline from Employee sort to Merge Join. In “Input Output Selection” popup select Output as “Sort Output” and Input as “Merge Join Left Input”. In Input user has two options as

“Merge Join Left Input”

“Merge Join Right Input”

Using this two options user can specify whether the input needs to be considered as left or right side dataset result.

Now you need to drag the pipeline from other “Sort transformation” and connect it to “Merge Join Transformation” as second input. While connecting the second input to the Merge Join, it will not ask for the input type as you have already provided it for the first pipeline, so by default it will select the other type of input to the Merge Join. i.e. Left or Right accordingly.

9. Merge Join Transformation Editor:

In order to configure merge join double click on the “Merge Join Transformation” to open the Editor.You need to provide the Join Type to specify which type of join operation you want to perform on the selected dataset.

Different Join types are:

Inner Join

Left Outer Join

Full Outer Join

Here we will select the “Inner Join” as Join Type as we need to display data from both the datasets. Select “Dept_No” as Join Key as it is the common field on which we can merge two datasets data.

10. Result table creation:

We need to create a table to store the output result into Test database as per the script provided.

In this way we get the Merge Join result by combining both the tables data based on common data, such that it becomes easier to navigate information from the single merged table, instead of referring two different tables and link the related data.

Lookup transformation performs lookup operation by joining data in input columns with reference table dataset columns.Lookup can be used to access addition information from the reference dataset based on the matching criteria Reference dataset can be OLEDB table, Excel file or cache file, or SQL query result.

Use of Look up Transformation:

In my source system (table), I have all the product with their details. Somehow I have products which belongs to the country which doesn’t exist in my reference (master) table. I assigned a job to rectify those products. I need to design ETL which gives me those records whenever we import products to our target database (table). So here, I am going to use “Lookup no match output” to capture those records by following steps:

Let’s take an example to easily understand how to use Lookup Transformation in SSIS.

1. Create Source Connection:

Select and drag “Data Flow Task”, from “Control Flow Items” to designer surface. Then double click it and Create a New OLEDB connection.

2. Create sample tables

Now we will create tables named ‘LKP_Countries_Source’ and ‘LKP_Countries’ into Test Database from the given script.

Now you Need to select the proper OLEDB connection in “Connection Manager” tab and the source table for lookup task.

4. Columns Selection from Source Table:

Select the columns to use as output columns.5. Lookup Transformation Editor:

Here, I have added “Lookup Data Transformation” Task to designer tab and click on edit to configure the Lookup transformation.6. Handle No Match output:

Now we need to configure various sections in the “Lookup Transformation Editor”.In General section, select “Redirect rows to no match output” to handle the unmatched data from the lookup task.

Here we will need to select Cache mode as “Full Cache”. This option is used to improve the performance while handling large scale of data.Keep connection type as “OLEDB Connection Manager”, as we are using OLEDB source. When you use Cache File as data source then you will need to select “Cache Connection Manager”Last option provide various ways in which not matched data can be handled.

Ignore failure – ignores the failure and executes the next task.

Redirect rows to error output – moves the not matched rows to red output to handle them separately.

Redirect rows to no match output – switches rows to the secondary output, and user can handle it differently to matching data

7. Set Connection Manager for Lookup table:

In Connection section select the reference table with proper connection. This list will get compared with source dataset for matching the data.

8. Column mapping for Lookup table:

In Columns section, select the available input columns and map it with the available lookup columns. This will create a join between 2 source datasets.We have used “Full Cache Mode”, so Advanced section will be disabled and in “Error Output” keep fields as it is.

9. Input Output Selection Setup:

Select new “OLEDB Destination” transformation and drag it to the designer surface. Drag green arrow from “Lookup Transformation” Task to “OLEDB Destination” and provide “Lookup Match Output” as Output and click OK.

10. Create Output table for Match and Not Matched Data:

Now we will need to create output tables to store the matched as well as not matched result.

11. Data Mapping for Result table:Now I need to provide mapping for the Output Table to store the Matched Data.

12. Complete Data Flow for Lookup Transformation:

In order to handle the “Not Matched data”, provide the link of Not Matched data to OLEDB Destination table “LKP_Output_No_Match”. It will store the not matched results.

13. Package Execution and Result:

Now let’s execute the Package and check with the output inside the tables we have created to store the result as in“LKP_Output_Match” table for Matched Data and “LKP_Output_NO_Match” table for Not Matched Data.