Convert CSV to Excel

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:

1. 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"

Excel connection Manager

Connection Properties Window

Its Important to change the Delay Validation to TRUE

Build the expression for ExcelFilePath

@[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) + "\\" + REPLACE( @[User::FileNameString] ,"CSV"," xls" ) + ".xls"

Data Flow Task

Connection Properties Window

Flat File Source

Use general setting on flat file source

Data Conversion Task

Select Input column and change data type to Unicode string [DT_WSTR], to convert non-Unicode string to Unicode string.

Excel Destination

Use general settings on Excel destination.

In some case we might still get following error like

[SSIS.Pipeline] Error: "component "Excel Destination" (113)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

[Excel Destination [62]] Warning: The external columns for component "Excel Destination" (62) are out of synchronization with the data source ……

In that case try changing the component properties on Excel destination. Change ValidateExternalMetadate to FALSE.