July 20, 2010 at 12:42 pm
Back in the days, I have always used SSIS lookup transformation to transform non numeric key value to numeric surrogate keys when I am loading the datawarehouse fact table.
At my new job, the data source was mostly numeric key, and also with the new 2008 MERGE syntax, I decided to use MERGE to do my staging -> DW fact loading in basically one statement, and it worked very well.
However, we have changed the source system, and now I have to do some key lookup as well.
Is there a way to use it along with the existing merge statements?? I am asking coz MERGE seems to be mostly a ANSI SQL set operation, and I am not sure how to incorporate that along with the lookup tranformation, which mostly perform the lookup in "intermediate data format" in a stream ....
notice I can't do the merge first, and lookup later, as the data model is different (the destination column is non numeric vs numeric)
what am i missing? thanks
July 20, 2010 at 2:26 pm
Merge statement can be used alongside SSIS, check here for details... http://technet.microsoft.com/en-us/library/cc280522.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2010 at 3:06 pm
Sorry I guess my question wasn't clear.
I do know that SSIS can do merge statement via Execute SQL task, however, the format of my data is different mid ETL compare to the source or destination.
For example, my source column is a varchar, while the same column in my destination is an int, to go with the surrogate integer dimension key principle in datawarehouse.
But I am not able to do that in one merge statement, as that specify my source table and target table specifically, and not to any of the lookup output stream I am doing....
??? is that right?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply