I need to associate customers' order with their "level" (Silver, Gold, etc.) when they placed the order:
CRM server::CRM db::CRM table ---------- CustomerID PreviousLevel NewLevel NewLevelGrantedOn Order server::Order db::Order table ---------- OrderID CustomerID OrderPlacedOn
In an SSIS package I did this:
- Execute SQL against Order db to extract the orders and put them in an object variable;
- Loop through each order using "foreach container", in which I put a data flow task
select top 1 * where CustomerID = ? and LevelGrantedOn < ? order by LevelGrantedOn descthat extract data from CRM db (both parameters come from step one), derive some columns and write the output to another table.
There are more then twenty thousand records in the order db, which means the data flow task will be executed for more than twenty thousand times. The CRM db will be queried for more than twenty thousand times, too. It takes more than an hour to do these.
Can I utilize some built-in features to speed up these (or do it in a "smart" way)? And, is an hour a long time, in the context of ETL and / or SSIS?