SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing Multiple Excel Files


Importing Multiple Excel Files

Author
Message
olafdedig
olafdedig
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 92
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.
keymoo
keymoo
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 541
You could use the File Watcher Task
See http://www.sqlis.com/23.aspx



olafdedig
olafdedig
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 92
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
keymoo
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 541
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
olafdedig
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 92
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
keymoo
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 541
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
olafdedig
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 92
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
olafdedig
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 92
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 Smile

Olaf
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search