5

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?

1

2

3]

4]

5]

6]

7]

8]

9]

10

11

12

Improve this question
1

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for your answer, I am still getting an error. Please refer to my question, I just changed it.
Do your target files have a sheet called Sheet1 ?
I have added some detail to my answer. Not sure what your latest error (Sheet1$) is due to. It could be because columns don't match. Dynamic access to excel can be tricky.
1

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

Update 1

Many things you should try:

  1. Install Access Database Engine

Download Link: Microsoft Access Database Engine 2010 Redistributable

  1. Run Package in 32-bit mode

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

  1. Check that the Sheet1 exists in all templates

  2. Assign a default value for the filepath variable

Improve this answer

17 Comments

Thank you for your answer. Sorry, I should have been more clear. Yes delay validation is set to True and I have also played around with switching to ExcelFilePath property instead of ConnectionString as a lot of people suggested this when I googled the issue but I was still getting an error so I switched back. I have changed it to ExcelFilePath now and I have rewritten my question with the error I am getting now. Please refer to that and see if you can give any suggestion.
Are you running the package in 64-bit mode? What are the sheet names in your excel templates?
Try downloading the access database engine
I already have that installed. That is how I am able to work with xlsx files. Its called Microsoft Office 12.0 Access Database Engine OLE DB Provider right?
I hate Excel and offer my clients a CSV file that will open in Excel. So much easier to deal with on both incoming and outgoing formatting.
|
1

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.

Improve this answer

4 Comments

Hehe you make me spend more than an hour on that. Anyway i will edit my answer and add all suggestions that we tried in the comments. So it will help future readers. Good luck
I edited my answer. Anyway for next time, assign expression for connection manager after finishing the design of your package. Do it as the last step
Finally, don't forget to accept an answer. even if you choose to accept yours
i'll accept your's for trying to help me out. I appreciate it, thank you so much! Please go ahead and add my answer to the list of your answer as that was the solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.