use of rowset destination in SSIS

 I want to create country specific text file dynamically contains the details of their employee having suffix as country name.

Suppose this is my Table named as ’CountryEmpDetails’

 

1>First open data flow task in SSIS.

Choose OLEDB Source from toolbox and open it. Then set the connection to database where your ‘CountryEmpDetails’ resides and select SQL command in Data Access Mode and paste the query as written here.

 

Select distinct Country from CountryEmpDetails

 

2>Create a variable names as ‘country’  and set its datatype as ‘object’

3> Then open rowset destination and set ‘country’  under VariableName property.

4> Define another variable ‘countryname’  and set its value null.

 

5>Open ‘ForEach Loop Container’ and set the property as shown below in the figure:

Select ‘Foreach ADO Enumerator’ and select ADO object source variable

as ‘user::country”

Then in the variable mapping part select “user::countryname”

6>Now create a separate folder name anything but in my case, I named it ‘Country’

 

And there I create one file contains the field like ‘name’,’designation ‘and ‘country’ and it is tab separated text file and saved it with any name . Here I saved this file as ‘EmpDetails.txt’

 

7>Under ForEach loop container drag another DataFlow task

8> Under Data Flow Task drag OLEDB SOURCE and FlatFile Destination.

Under OledB Source paste this query…and then click the parameter button to set the parameter value as ‘user::countryname’

 

9> Under Data Flow Task, first set the connection manager and browse the

EmpDetails.txt File and mapped the columns.

And then to make it dynamic, you have to follow the below mentioned steps.

a>  Define another variable ‘FilePath’ .

b>  Then  select the Filepath variable and go to property section on right side

 

Then Set the EvaluateAsExpression As ‘TRUE’ and open the wizard in expression part by clicking on  

 

And under expression part set the value like “Filepath”+countryname.

For ex:  "C:\\Documents and Settings\\mshukla\\Desktop\\Country\\EmpDetails_"+ @[User::countryname]+".txt"

c>  Then go to the FlatFileConnection Manager and select property by right click on it.

  Select Expression and then click on

 

After that Select the variable as “user::Filepath” in it.

 

 

d>Now run the package and u find that in your folder, 4 files creates dynamically having prefix as countryname and contains the contents of respected country.

If you see , EmpDetailsIndia.txt is created and contains the data for the employee belongs to country India.