May 23, 2008 at 9:01 pm
I have an excel file which has 3 columns (employee Name, Employee Id, DeptID). "DepID" column in the excel file is empty. The goal is to import these columns in sql server database. So far easy.
The challenge is to pass the parameter for "Deptid" (since DeptId is an empty column in xcel source) and whatever variable is passed, the destination column in sql server should be updated with that variable for all columns.
Here is the format of excel file:
employee name employee id DeptID
abc 123
pkg 234
fgt 214
The destination table in sql server should look like this:
employee name employee id DeptID
abc 123 1
pkg 234 1
fgt 214 1
The number "1" is being passed at the SSIS using a variable.
Any help is greatly appreciated.
Thanks,
unix guru.
May 25, 2008 at 3:43 pm
On the control flow screen put a data flow task and double-click on it to get to the data flow screen.
Right-click in the open space and select variables. Then add a variable with the hard-coded value (1) for DeptID.
Place an Excel Source on the screen. Add a Derived Column step and connect them. Derive a column with the new value of DeptID and give it a value of the variable you defined. You can expand Variables, then drag your variable to the Expression box.
Add an OLE DB Destination for your table and connect them. On the mappings page, connect the new DeptID to the DeptID in the table.
May 25, 2008 at 8:28 pm
Thank you so much. It worked!:)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply