excel to sql

Question

excel to sql

bang.prashant

Ten Centuries

Points: 1100
More actions
September 10, 2008 at 11:18 am

#382614

source file - excel file ( with same format) with different name ( all file are in a folder with name like qa.xls , store.xls,planning.xls)
destination - sql server
how to build source connection every time different and import it in destination.
in short how to get file name and pass it as a source?
thanks

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

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

notes4we SSCommitted Points: 1565 More actions · Answer 1

Hi,

I did not get your question exactly, but as far as I understood, you are asking how to connect to different excel files everytime. I guess it means, different excel connection managers.

For that, you just have to drag and drop the Excel Source in the Data Flow Task.

Double click it and you will have to specify connection manager. Over here, you can click on new on the right hand side and in the Excel file path, you can browse and select your excel file. Click on first row has column names, if your excel files have that.

Then, hit ok and then in the Excel Source Editor, you have to specify the excel sheet name. You will find the excel sheets associated with the particular excel file that you might have selected as a connection manager in the previous step.

Hope this helps. I am also working using Excel source and SQL Server destination.

Good Luck.

bang.prashant Ten Centuries Points: 1100 More actions · Answer 2

thanks for reply.

but i don't know filename eachtime.

meaning how to get filename and pass it as a source for dataflowtask.

Mukti SSCrazy Points: 2826 More actions · Answer 3

For making your excel connection dynamic you can use expressions. For this first create a package variable that will hold the filepath.

Now Create a Excel Connection, in the properties -> Expressions -> select conneciton string and use the varible to set the connection string.

Next step is to use foreach file enumerator to get the file path for each execl file in the directory.

Now you can have dataflow inside the foreach loop to load that data

HTH

~Mukti

bang.prashant Ten Centuries Points: 1100 More actions · Answer 4

for first time it is working and next time it is not

here wht i did

1. created variable and it value is filepath - c:\test\qa.xls

2. given this variable in excel connection string

3. for each loop did all the things

4. hence it exceucted and all files are imported in c:\test folder.

5. now i deleted all files and created new file called qa12.xls

package failing...

jmuldoon Old Hand Points: 377 More actions · Answer 5

The way that I've done this successfully in the past was to use the expressions to replace the Excel Filename property with the filename from some variable.

My observation has been that the connection string property for a spreadsheet does not ever contain a filename, even when using a static filename. (but that is not to say that SSIS does not add the filename property to the connection string at runtime to produce it by the time it opens the spreadsheet).

At least that how i've done it in a ForEach container and it has always worked. Never had to do anything with the connect string property, only the filename property.

I'm sure that either method would probably work (creating an expression for the filename or conenct string property), but what I'm having a little trouble with is that no one mentioned that one would have to add simething like "file = " to the connection string.

Jon Ochmanek Ten Centuries Points: 1165 More actions · Answer 6

Is you're package actually failing (hard stop) or is it just not loading the file? There's a difference.

Make sure you have a wildcard in your foreach container's string (eg. c:\test\qa*.xls).