Dynamic Excel Sources

Please help....

I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.

My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.

I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.

Thanks,
Dave

You could use a Foreach Enumerator to loop through each file with the same structure and load it to the destination table, by placing the data flow within the loop container.

-Doug

|||

How would I go about getting the file name to change appropriately with each file. I have the foreach creating a variable called FileName. I have a script task picking off the information in the filename (format: <vendor #>_<start date>_<stop date>.xls). I then put vendor#, start date, and stop date into package variables. I guess my main concern is not knowing if I'm using expressions right. I mapped the ExcelFilePath and ServerName to user::FileName. With the file changing, do I have to set anything else to keep errors from occurring?

Thanks,

Dave

|||

Dave,

You shouldn't need to worry about the ServerName property for Excel. As for ExcelFilePath, I would want to make sure that the ConnectionString property was updated immediately and automatically when I provide a new value (outside the UI) for ExcelFilePath. Otherwise you could use an expression on ConnectionString, instead of simply mapping ExcelFilePath to User::FileName.

Updating the ConnectionString for Excel by using an expression has been discussed here previously. The expression shown in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=103273&SiteID=1 should work for you when you insert your FileName variable at the proper place.

-Doug