I've a SSIS Package with a final result of 5K records, but it runs for like 6 hours. What did we build?
We have setup a DFT with an ODBC Source that doesnt do anything more then
SELECT ID FROM TABLE A
(40K Records, I need this ID as a parameter for my SQL command).
Next Step is a Recordset Destination where i save all the ID's as an object.
After these steps it continues in a Foreach loop container with Enumerator Foreach Ado Enumerator. Ado Object variable is the object i created in the first steps.and the mode we use is Rows in the first table. Then we setup a variable mapping in the container named Input.
In the container we put a another DFT.
ODBC Source.Sqlcommand:
"select * from Function_B("+ (DT_WSTR, 6) @[User::Input]+",1,0,1)"
The last 3 parameters are always the same only the ID does switch.
In the DFC i put a ODBC Source with the following SQL Statement:
select * from function_B(0,1,0,1)
and an OLED DB Destination.
It works, but for something as small as 40K records and a final output of 5K should be able to do that much faster in my opinion than the 5/6 hours it runs now.
Thankyou in advance.