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

Stephanie
  • 496
  • 7
  • 26
  • Possible duplicate of [Dynamically assign filename to excel connection string](https://stackoverflow.com/questions/21536719/dynamically-assign-filename-to-excel-connection-string) – Nick.Mc Jan 24 '18 at 00:22

3 Answers3

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Thank you for your answer, I am still getting an error. Please refer to my question, I just changed it. – Stephanie Jan 24 '18 at 16:35
  • Do your target files have a sheet called `Sheet1` ? – Nick.Mc Jan 24 '18 at 22:25
  • 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. – Nick.Mc Jan 24 '18 at 23:51
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

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 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. – Stephanie Jan 24 '18 at 16:34
  • Are you running the package in 64-bit mode? What are the sheet names in your excel templates? – Hadi Jan 24 '18 at 16:39
  • I found that some links are suggesting recreating the data flow task!! Try it – Hadi Jan 24 '18 at 16:44
  • Yes 64, I didn't change sheet name at all. It should be default to sheet1. I recreated the data flow task. When mapping to Excel Destination, Name of the Excel sheet is coming as "No tables or views could be loaded" Seems like it doesn't recognize any excel file. I wonder if its due to the variable I created because when I created expression it changed the ConnectionString to Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\.xlsx;Extended Properties="Excel 12.0;HDR=YES" which has no file name... – Stephanie Jan 24 '18 at 17:11
  • Change the runtime to 32-bit from the project properties – Hadi Jan 24 '18 at 17:26
  • I changed it but same error when I try to map Excel Destination: Error at Data Flow Task [Excel Destination [37]]: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database. – Stephanie Jan 24 '18 at 17:36
  • 1
    Try downloading the access database engine – Hadi Jan 24 '18 at 17:50
  • @Stephanie i think that you have to download and install the AccessDatabaseEngine as mentioned above https://www.microsoft.com/de-de/download/details.aspx?id=13255 – Yahfoufi Jan 24 '18 at 19:03
  • 1
    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? – Stephanie Jan 24 '18 at 19:24
  • 1
    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. – KeithL Jan 24 '18 at 20:27
  • @KeithL this is really confusing. Is there anything else we have to check. I think that we are missing something. – Hadi Jan 24 '18 at 20:34
  • @Stephanie try installing the AccessDatabaseEngine 32 and 64-bit (both) – Hadi Jan 24 '18 at 20:36
  • How about putting in breakpoint and checking locals to make sure the variables are doing what you want. – KeithL Jan 24 '18 at 20:40
  • Also, a good way to do Excel destination is to use SSRS and schedule distribution of a report. – KeithL Jan 24 '18 at 20:44
  • I cannot do CSV because I have to password protect the files. I tried the same method to see if I can create multiple Flat Files and I was able to do that but with Excel its just not working. For the same reason, I cannot use SSRS. I will go ahead and share images and maybe it can give some insights. – Stephanie Jan 24 '18 at 22:07
  • Added pictures of my process – Stephanie Jan 24 '18 at 22:48
  • I have fixed the problem by assigning a default value to FileName variable. After assigning it, it generated two files as expected, one for ABC and other DEF. – Stephanie Jan 24 '18 at 23:51
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.

Stephanie
  • 496
  • 7
  • 26
  • 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 – Hadi Jan 25 '18 at 00:08
  • 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** – Hadi Jan 25 '18 at 00:15
  • Finally, don't forget to accept an answer. even if you choose to accept yours – Hadi Jan 25 '18 at 00:17
  • 1
    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. – Stephanie Jan 25 '18 at 18:08