Microsoft SQL Server Integration Services

In real world scenario we end up in dynamically converting multiple CSV or TXT to excel and renaming them with same file name.

At the end of demonstration package looks like below.

Create Variables:

Create a variable (UploadFileName) that reads values from SQL table. In order to read values from SQL with full resultset it should of data type Object.

2. Create a variable FileNameString with String data type. This variable is used store file name in string format as we cannot use Object data type in expression.

3. WorkingDirectory for location of the file.

Excute SQL

This task is used to get file names form SQL table.

Edit Excute SQL task

· Change the Following in General Tab

1. Result Set type - Full result set

2. Define source connection

3. Enter the SQL statement

· Result Set Tab

For Loop Container

1. Change following in Collection Tab.

Enumerator – Foreach ADO Enumerator
ADO object source variable – USER::UploadFileName
Select Rows in the first table

Here in variable Mapping we map variable UploadFileName to FileNameString. To convert from Object data type to String data type.

Excute SQL Task

This task is important as we create Excel file and define output excel structure.

1. Change the Following in General Tab

· Connection Type – Excel

· Connection – Define excel connection manager

· SQL Source Type – Create a excel destination table

CREATE TABLE `Excel Destination` (

`ID` LongText,

`TItle` LongText,

`Forename` LongText,

`Surname` LongText,

`Address1` LongText,

`Address2` LongText,

`Address3` LongText,

`Town` LongText,

`County` LongText,

`Postcode` LongText,

`Telephone` LongText

)

Flat/CSV Connection Manager

Connection Properties Window

Build the expression for connection string

@[User::WorkingDirectory] + RIGHT("0" + (DT_STR, 4, 1252) YEAR( GETDATE() ),4) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE()), 2) + "\\" + @[User::FileNameString] +".CSV"