Importing Multiple Excel Files

Question

Post reply

Importing Multiple Excel Files

olafdedig

Right there with Babe

Points: 768
More actions
July 3, 2008 at 2:59 am

#129809

Hello
I would like to know if SSIS is able to do the following:
Have SSIS poll a folder and if a new Excel file has been copied there then import that file in SQL tables, the Excel files would not ahve the same name, but would have the same file structure.

Viewing 8 posts - 1 through 7 (of 7 total)

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

keymoo SSCrazy Points: 2975 More actions · Answer 1

You could use the File Watcher Task

See http://www.sqlis.com/23.aspx

olafdedig Right there with Babe Points: 768 More actions · Answer 2

Hello

Thanks for the link, the controlflow can let me monitor the folder, and as far as I can tell returns the name of the file for later use, how do i then go about using that filename to import the excel file, how to i modify the excel source in the data flow items to then import that file.

keymoo SSCrazy Points: 2975 More actions · Answer 3

You could put the name of the file into a Package Variable and then reference the package variable to set the Excel Source using an Expression in the connection manager.

olafdedig Right there with Babe Points: 768 More actions · Answer 4

Thanks for the help so far,

i have not used variables before in the SSIS packages before, so this is what I have done (after going through a few other posts as well)

I have first now tried a for each loop created a variable in the for each loop that then should be used to cycle through the files in that folder. but when i update the excel connection manager with the expression "conenction String and set it to the variable I get the following error.

TITLE: Package Validation Error

------------------------------

Package Validation Error

------------------------------

ADDITIONAL INFORMATION:

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

So i open up the excel connection manager but the location is now blank and will not run - any hints

keymoo SSCrazy Points: 2975 More actions · Answer 5

Try creating your variables at the package level. You may need to delete your variables and recreate them with the mouse focus on the background of your package.

What property of the connection manager are you setting in the Expressions property? You should be using ConnectionString.

olafdedig Right there with Babe Points: 768 More actions · Answer 6

i am using the Connection String from the drop down then set it to the variable

as far as the variable goes (the one that contains the file name) I create it in the foreachloop editor wherer is says variable mappings, I select new variable - will try craeting it at the package level

olafdedig Right there with Babe Points: 768 More actions · Answer 7

Hello

I have found the reason why I was getting errors. I had been able to get the for each loop workong on normal flat file so i assumed that the connection string expression wouild be the same. I know now that it is not.

For Excel the connection String expresion in the connection manager properties need to look like this. Two variables are needed one is the file name and the second is a variable that contains the extended conenction details.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelImput] + ";Extended Properties=\"" + @[User::extendedPro] + "\""

Thanks for all the help, problem solved 🙂

Olaf