SSIS import Excel Files

Question

Post reply

SSIS import Excel Files

bob_kudulis

Say Hey Kid

Points: 690
More actions
April 20, 2010 at 3:25 pm

#219492

I would like a package to go out to a directory and loop through the Excel files and bring them into the DB. If I convert the files to TXT I am able to get it done with a flat file source but when I attempt to do it with an excel source I cannot. I have tried changing the Excel source to "Table Name or view name variable" and setting the variable to the variable name that is set in the collection of the Loop container. When I do this it says "A destination table name has not been provided".
I have set the Access Mode to a specific file and when the job runs it brings the same file in 16 times which is the number of excel files in the folder.
I'm all twisted up and just cannot seem to get the Excel source to use the loop.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Aside from the Excel filename, you should also provide the name of the sheet where the data is.

If this changes from file to file, you are in for a world of pain 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 2

I have set the Access Mode to a specific file and when the job runs it brings the same file in 16 times which is the number of excel files in the folder.

The fact that its looping through the same file is probably because you did not change the Connection String property for the Excel File... You need to use an Expression and Replace the Actual Filename in the Connection String with a File Name Variable using expressions.

check out the link below... Let me know if this helps

http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html

bob_kudulis Say Hey Kid Points: 690 More actions · Answer 3

I get an error when I put this into the connection string property of the connection manager.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 4

You'll need to add an expression to the Excel connection manager. ExcelFilePath is the expression you'll need to look for. You'll need to include the full path of the file as well as the file name.

bob_kudulis Say Hey Kid Points: 690 More actions · Answer 5

I added the variable value from the Loop container to the connection string property of the Excel connection manager and received this when I ran it.

Error: 0xC0024108 at Excel, Connection manager "Excel Connection Manager": The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error: 0xC0017004 at Excel: The result of the expression "@[User::FlatFile_Source]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.

Error: 0xC0202009 at Excel, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.