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.

 

 

 

 

 



 

 

 

 

 

 

 

Set the dynamic date in variable of SSIS

Suppose if you need to create dynamic file which contains the file name with current date and time associated with it  in SSIS,then you have to follow below steps:

1>Define a variable ‘FilePath’ and set its value as null.
   

 

 

  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”+"ddmmyy"-"hhmm".txt

For ex:  "C:\\Documents and Settings\\mshukla\\Desktop\\Country\\EmpDetails"+ "_" + (dt_str,4,1252)datepart("yyyy", @[System::StartTime]) + "_" + right("0" + (dt_str,4,1252)datepart("mm", @[System::StartTime]),2) + "_" + right("0" + (dt_str,4,1252)datepart("dd", @[System::StartTime]),2)+"_"+".txt"

 

 

 

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

Select Expression and then click on

 

Then you get this window

After that Select the variable as “user::Filepath” under connection String property 

 

 

Batch file to copy file from ftp location to your local folder

//Save this file  with .bat extension

@Echo Off
Set _FTPServerName=ftpservername
Set _UserName=username
Set _Password=password
Set _LocalFolder=C:\sample\demo  \\ path of localfolder
Set _RemoteFolder=In/demo1 \\ path of server folder
Set _Filename=*.gz
Set _ScriptFile=ftp
:: Create script
 >"%_ScriptFile%" Echo open %_FTPServerName%
>>"%_ScriptFile%" Echo %_UserName%
>>"%_ScriptFile%" Echo %_Password%
>>"%_ScriptFile%" Echo lcd %_LocalFolder%
>>"%_ScriptFile%" Echo cd %_RemoteFolder%
>>"%_ScriptFile%" Echo binary
>>"%_ScriptFile%" Echo prompt n
>>"%_ScriptFile%" Echo mget %_Filename%
>>"%_ScriptFile%" Echo bye
:: Run script
ftp -s:"%_ScriptFile%"

Expression used to set the date format like ‘ddmmyy-hhmm’ in SSIS

RIGHT("0" +(DT_STR,4,1252)DATEPART( "yy" , getdate() ),2) +

RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2) +

RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)+"_"+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2)

+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2)

Dynamically insert row into column

--I have referred AdventureWorks database to demonstrate this example

Suppose I have created a Demo1 table

Create
Table Demo1 (Address1 Varchar(50),Address2 Varchar(50),Address3 Varchar(50),Address4 Varchar(50),Address5 Varchar(50),Address6 Varchar(50),
Address7 Varchar(50),Address8 Varchar(50),Address9 Varchar(50),Address10 Varchar(50))\

 
Select * from Person.Address where AddressID<=10


--Then I want to refer AddressLine1 of Person.Contact table and insert this values into a single row dynamically


DECLARE
@TBLREPORT TABLE (ID INT IDENTITY(1,1),AddressID int,AddressLine1 NVARCHAR(MAX))

DECLARE
@SQL NVARCHAR(MAX)

DECLARE
@ROW_1 NVARCHAR(MAX)

SET
@ROW_1=''

Insert
Into @TBLREPORT

Select
AddressID,AddressLine1 from [AdventureWorks].[Person].[Address] where AddressID between '100' and '109'

SELECT
@ROW_1=@ROW_1+''''+AddressLine1+''' AS [Address_'+CAST(ID AS VARCHAR(2))+'],'FROM @TBLREPORT ORDER BY ID

SET
@SQL=''

SET
@SQL= 'INSERT INTO Demo1 SELECT '+ SUBSTRING(@ROW_1,1,LEN(@ROW_1)-1)

EXEC
(@SQL)

Select
* from Demo1

--Now you check  the output

Create Dynamic Table

DECLARE @TBLREPORT TABLE (ID INT IDENTITY(1,1),FirstName NVARCHAR(MAX),LastName NVARCHAR(MAX),EmailAddress NVARCHAR(MAX))

---- TBLREPORT has no physical existence in Database
Insert Into @TBLREPORTSelect FirstName,LastName,EmailAddress from [AdventureWorks].[Person].[Contact] where Title='Mr.'
Select * from @TBLREPORT