May 21, 2015 at 5:32 pm
I have a flat file source which contains various IDs , their corresponding pin number(which can be same for different IDs) and a code(code can be same for different Ids). The data is being loaded from flat file to a destination table. Now, I need to make changes to the data flowing from the source before getting inserted into the destination table, in such a way that, all the IDs that have a particular pin number should have their code updated to 'S'.
The logic i have used to do this is to create a execute SQL task where i write a query to pull the pin numbers for which the data needs to be updated. I created an object variable to hold the result set of this query. Then I created another variable called pin number. Then I created a For each loop container, where I have put a DFT. In the for each loop container, i have selected the For each ADO Enumerator and the ADO source variable as the object variable i have created earlier. In the variable mappings I have selected the user::pinnumber with index 0. In the DFT, I am writing a derived column between the source and destination. The condition I wrote is:
PinNumber == (DT_I4)@[User::PinNumber] ? "S" : [Code]
The pinnumber on the left hand side is int. And the pinnumber on right hand side of the condition is char. So I have typecasted it.
The reference article I took for this is:http://www.timmitchell.net/post/2013/05/28/using-the-ssis-object-variable-as-a-result-set-enumerator/
But the records are not getting updated for one pin number but getting updated for one pin number. Is there anything that i need to change? Where am i doing wrong??
I tried to debug setting the breakpoints for the for each loop container. I have 2 values for the pin numbers and the values are getting populated at the end of each iteration.
July 7, 2015 at 9:15 am
It could be any number of issues. Can you add a data viewer to include both the PinNumber column and the PinNumber variable into the data flow? This would allow you to see immediately which rows should have been updated. If it's right in the data viewer, there's something further downstream in the process that needs to be addressed.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply