How to export data from SQL table to multiple excel files in SSIS?

Question

I created an SSIS package that extracts data from SQL and load it into excel. I am having problem making this package dynamic and loading data into multiple excel files.

I first created an execute SQL task that holds all the file names with SQL code: SELECT FileName FROM Files and its result set will be stored in a variable FileNameObj. Then I created a Foreach Loop Container and added a Data Flow Task inside.

Foreach Loop Container setting: Foreach ADO Enumerator, ADO Object source variable - FileNameObj, Variable mapping - FileName and Index - 0. Under data flow task, I have added an OLE DB Source and Excel Destination pointed to file path: C:\Test\ABC.xlsx .

Here is the Connection Manager Property:

ConnectionString: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\ABC.xlsx;Extended Properties="Excel 12.0;HDR=YES";

DelayValidation set to True

ExcelFilePath is C:\Test\ABC.xlsx

Then I created an expression with Property ExcelFilePath and Expression: "C:\\Test\\"+ @[User::FileName]+".xlsx" Then my ConnectionString changes to: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\.xlsx;Extended Properties="Excel 12.0;HDR=YES";

I have created all my files templates (same structure for all) in test folder already. I get the following error:

Error at Data Flow Task [Excel Destination [131]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [Excel Destination [131]]: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

What am I doing wrong? Or is it even possible to do this in SSIS?

Answer 1

Update 1

Many things you should try:

1. Install Access Database Engine

Download Link: Microsoft Access Database Engine 2010 Redistributable

2. Run Package in 32-bit mode

In the Project properties, change the 64-bit Runtime property to False

3. Check that the Sheet1 exists in all templates

4. Assign a default value for the filepath variable

---

Initial answer

Dynamic Excel connection string

First of all, the excel connectionstring for .XLSX format is like the following:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test\\"+ @[User::FileName]+ ".xlsx; Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"

And the recommended way is (what @Nick.McDermaid mentioned), to assign the value to ExcelFilePath property

But you must take into consideration many other things:

1. All excel files must have the same structure, if not this package will always fail.
2. Set the Data Flow Task Delay Validation property to True

References

• Excel connection strings

Answer 2

See here: Dynamically assign filename to excel connection string

Use ExcelFilePath, not ConnectionString

Just to clarify:

• If you are loading the same data with same columns into multiple excel sheets, it will be much easier to just export once and do a filecopy on the document

• If each sheet has different columns then this is not going to work - each data flow needs to have identical columns each time it runs

• If you are loading identical columns but different filtered data then you may want to consider why you are doing this. Is it to overcome row limits in excel? If so, export to CSV instead. Is it to generate custom reports to users? Consider a reporting tool instead.

Answer 3

I have found a solution to my own problem: Assign a default value to FileName variable. I assigned ABC and it worked. Generated two files as expected, one for ABC and one for DEF.