SSIS performance tuning question

Question

Hi,

I am trying to bascially do stuff in data task flow which starts by a flat file import .. after that i try to call a stored procedure which has a lot of if  statements and stuff for some business conditions basically ...trying to make insert in different tables according to some conditions(inside the SP).. now the problem is that this is really slow for huge file set say 1 million records and stuff and its understandable too because a oledb command tasks for each row in the dataset ..
              i mean i dont know what the other alternatives are i mean if i start using look up and stuff then it becomes too many conditions and stuff ...
what i had in my mind was whether i could write a SP where the data of the entire dataset via flat file could be taken as an input parameter as XML in sql server 2005 .. and then i could play around with data inside the sp by making bulk updates internally but problem is there is no SQL task inside the dataflow..
can anybody suggest something...
i mean other options are writing a script component and probably in pre execute get the connection of the database , and then call the SP probably but not sure whether i want to make that task so so complex ...and dont know how flexible it would be ...

Thoughts /ideas welcome !

Answer 1

Try to remove the OLEDB command component and store the results (of the component acting as input for OLEDB Command) in a staging table.
Then try to include this staging table inside the SP logic (using Inner Join).
Could you share the SP logic at a higher level.

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 2

You have to go set-based operations! Row by row is a performance killer, it is like a cursor WHILE loop.

Can you upload the data into a staging table and change the sproc to do set-based processing?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 3

I cant use a staging table , its  a prod database and creation of objects is not possible...

Answer 4

i exactly had that in mind but that would need creation of a staging table going back to control flow and use a set based operation in a SQL task...i cant create a physical table here ...

Answer 5

Then create a temporary table (##)

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 6

i exactly had that in mind but that would need creation of a staging table going back to control flow and use a set based operation in a SQL task...i cant create a physical table here ...

That is very limiting.

Can you ask the DBA to create a staging database to support fast ETL  operations?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 7

lemme try that idea ...

Answer 8

Hi,

i would like to suggest you to use Nitesh approach with the row file destination. Save your result in row file destination and then in next DFT you just take the data from the row file as a source and dump the data.

Row file will provide you much faster loading.

 

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 9

You can speed up your data import from file to Database by setting Data Access Mode to "Table or View - fast load" if table name is fixed. In case table name is coming from variable than set Data Access mode to "Table name or View name variable - fast load"