Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Importing Multiple Excel Files Expand / Collapse
Author
Message
Posted Thursday, July 03, 2008 2:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 8:39 AM
Points: 24, 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.
Post #527853
Posted Thursday, July 03, 2008 5:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 106, Visits: 463
You could use the File Watcher Task
See http://www.sqlis.com/23.aspx



Post #527919
Posted Thursday, July 03, 2008 5:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 8:39 AM
Points: 24, 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.
Post #527955
Posted Thursday, July 03, 2008 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 106, Visits: 463
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.


Post #528005
Posted Thursday, July 03, 2008 7:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 8:39 AM
Points: 24, 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
Post #528058
Posted Thursday, July 03, 2008 8:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 106, Visits: 463
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.



Post #528081
Posted Thursday, July 03, 2008 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 8:39 AM
Points: 24, 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
Post #528139
Posted Friday, July 04, 2008 12:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 30, 2010 8:39 AM
Points: 24, 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 :)

Olaf
Post #528500
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse