October 14, 2009 at 9:46 am
Hi guys
I need your help, I need to do 3 different things in SSIS related with Excel Data source but have no idea if it's possible or how:
1. Let the source file be "Browsebale": I'm building a package for a user and I would like that the excel file of the data flow source could be browsebale by the user so he/she could find the file instead of having it hardcoded in a path. Is this possible? or maybe if this is not possible make a way of setting the file as a relative path in the same place the package is?
2. Get info from all sheets: There's also somoe ocations this excel files have 1 or more sheets of data, the number of sheets is variable, is there a way to tell also the excel data source to get information from All sheets in the file?
3. Transformation: Excel Files from the source have a column with a user name, my destination is into a SQL Server database and I would like to know if it's possible to read the user name from the excel source, find it's ID related to a User table in the same database, and depending if it exists just insert the ID and if not first insert the user into the table and then just give the ID?
Thanks Guys!
October 15, 2009 at 6:14 am
1. Let the source file be "Browsebale": I'm building a package for a user and I would like that the excel file of the data flow source could be browsebale by the user so he/she could find the file instead of having it hardcoded in a path. Is this possible? or maybe if this is not possible make a way of setting the file as a relative path in the same place the package is?
You can build a custom UI that lets you browse the file and then start the package with dtexec passing the file path as a variable. That is the only way I can think of.
2. Get info from all sheets: There's also somoe ocations this excel files have 1 or more sheets of data, the number of sheets is variable, is there a way to tell also the excel data source to get information from All sheets in the file?
This sounds to me like you have to code either a custom Data Source or you write a Script task in which you embed the logic. I don't think SSIS has this functionality built-in.
3. Transformation: Excel Files from the source have a column with a user name, my destination is into a SQL Server database and I would like to know if it's possible to read the user name from the excel source, find it's ID related to a User table in the same database, and depending if it exists just insert the ID and if not first insert the user into the table and then just give the ID?
1. Excel Source from which you read the User ID
2. Lookup on the table to check whether the user exists in the database
3. Match columns -> Do nothing (Go on)
4. No match columns -> Insert user into database table (and then go on)
Make sure you set redirect no match columns to output and not error.
HTH
Nick
October 15, 2009 at 7:58 am
Yeah i actually said users to copy & paste data in 1 sheet and always place the file allways in the same path cuz it was much more work doing all that stuff.
And for point 3 I used the lookups, didn't knew how they worked but thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply