I have a table_src
lookup table d_currency
I need to update/ insert the currency_sk into the table_dest using the lookup, . how can this be done. the Id's are already in the table_dest. how can i map the id's in both the tables
SQL Server Integration Services (SSIS) provides a data flow transformation called "Lookup" for this purpose.
In SSIS, create a data flow task in the control flow. In the data flow, create your source data flow. From the Data Flow Transformations, drop a "Lookup" from the "Data Flow Transformation" tools. In the lookup, choose Cache Mode ( I suggest "Full Cache" for a currency lookup because this has few records.) In connection choose your table or use your own SQL query if necessary. In columns, map your source column to the lookup (table or query) column. Choose the desired output. The output column will be available for your data flow destination.
I couldn't tell exactly what columns you are interested in from your questio, but I think this will provide your solution.
I have used the lookup transformation on a table to get the SK into a stg_table , then used the execute sql stagement to update the Fact table holding all the SK's . im talking records from 2003 to 2012 ~ 900 million records, the update takes 2-3 hrs (for 6months range)and as we are using 2005 it fails saying transaction_logs is full and i have had this problem on 2005 so im using only smaller subsets like 6months at a time .
Is there a quicker way, just a curious question.