2

I'm trying to load data from an excel file with a sheetname which is not static (sheetname contains yyyymmdd which would change with each file) into SQL database table. I followed the solution provided on How to loop through Excel files and load them into a database using SSIS package? but could only manage to get the first for loop working. When I'm trying to assign the user variable 'Sheetname' to Excel Source under the Data Flow task, I'm getting the error -

Error at CSSN_Invoice [Connection manager "TEST MKBS CONNECTION"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Invalid argument.".

Error at Data Flow Task [MKBS Sheetname [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "TEST MKBS CONNECTION" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed

The data flow task is working fine when a sheetname is picked as 'Table or View' and not as 'Table Name or View Name Variable'

Please help !

Hadi
  • 36,233
  • 13
  • 65
  • 124
Romanshu Goel
  • 103
  • 3
  • 9
  • Answer coming soon. It uses a script task. – KeithL Jul 14 '17 at 16:55
  • I got it working with the help of the original link that I posted - [How to loop through Excel files and load them into a database using SSIS package?](https://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package) - I missed out one step from that answer and that's why it was not working. But all good now ! – Romanshu Goel Jul 18 '17 at 04:34

1 Answers1

1

Create a data flow task to read sheet names into ADO object.

Data flow

First item is a script component as a source. I have a variable for connection string to the Excel Spreadsheet

connstr

Created an Output of SheetName

Output Setup

Here's the code to read tab names: C#

You are basically opening the spreadsheet with oleDB. Putting the table names into a data table

Looping through the data table and writing out the rows to output.

Make sure to close the Connection!!! This may cause errors later if you don't.

The next step is a conditional split as for some reason the result has duplicates of tab names and they all end in an '_'.

Conditional Split

Next step is deriving a column to clean the sheet name of exta "'"

DerivedCol

Create a Variable of type Object: I named mine ADO_Sheets

Insert a recordset destination object: 1. Set the variable to the variable you just created 2. Map the columns for clean Sheet

Now back to the Control Flow and set up a foreach loop control: enter image description here

Configure the foreach... Enumerator: Foreach ADO Enumerator Source: ADO_Sheets Variable Mapping: Set to a variable called SheetName

I have a Function Task inside the loop but it is more for ease of understanding, it could have been down in the variables: SQL

This variable is now your select for extracting the data off that page.

Last is the data flow task you want to run.

Lot's of work, but I use this so often I thought I would share!!!

KeithL
  • 5,348
  • 3
  • 19
  • 25
  • Hi KeithL,Thanks for your reply. From what I understand, your solution is to get the Sheetname from excel file. My problem is not about not able to retrieve the Sheetname, that I'm able to do using the link I provided in my original question. My problem is when I try to assign that variable to excel source under my data flow task. I followed your solution as well but I'm getting error on Script Component - **format of the initialization string does not conform to specification starting at index 0**. I have followed it exactly the way you have mentioned. Also, I'm using SSIS 2008. – Romanshu Goel Jul 15 '17 at 10:07
  • It seems like your problem is how you are using tab name. Try putting any expression on a variable that creates a sql statement using the variable with the table name. – KeithL Jul 16 '17 at 13:53