Excel File Creation In SSIS Package

Question

Excel File Creation In SSIS Package

Datta4u

SSC Veteran

Points: 293
More actions
October 5, 2010 at 3:12 am

#229979

Hi....
Friends..
I have to export data from sql table to Excel on certain condition so every time a new excel file must be created , i m new to ssis creation please anybody help me
thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Leo Suresh SSC Veteran Points: 221 More actions · Answer 1

Define a Excel Connection Manager with sample output file. Use script task to dynamically generate the Connection String and store it in a string variable. In the properties of excel connection manager choose expression and set the variable to ConnectionString property.

Then use an Execute sql task with ConnectionType as "EXCEL" and point out the excel conection manager as it connection. In the SQL statement provide the create table script.

Whenever the package runs, the dynamically generated connection string will be set to the excel connection manager and when the Execute SQL task fires the "create table" script against this connection a new file will be created.

Regards,

Suresh

Datta4u SSC Veteran Points: 293 More actions · Answer 2

Thanks for reply

But whenever i m trying to add value to connection string property of expression of Excel connection manager i m getting this error

Error at DataImportExcel [Connection manager "Excel Connection Manager"]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

plz if any sample for this or any site suggestion............

Leo Suresh SSC Veteran Points: 221 More actions · Answer 3

The error says that the connection string is not well formed...

Try to set the variable value as follows in script component:

'For xls:

Dts.Variables("strConnectionString").Value = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\sample.xls;Extended Properties=""Excel 8.0;HDR=YES"";"

'For xlsx:

Dts.Variables("strConnectionString").Value = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\sample.xlsx;Extended Properties=""Excel 12.0;HDR=YES"";"

Set this variable strConnectionString in the ConectionString expression of Excel file connection manager and check