Post reply

Stored Procedure Running Much Slower in SSIS Package

  • August 20, 2015 at 3:12 pm

    #311003

    Hello,

    I am hoping I might get some advice on what I should be looking for to troubleshoot this issue I am experiencing with a package I have created.

    I am reading time punches from a .csv file and converting it with my data flow to the data type the procedure uses. I'm then dumping the results into a staging table.

    In the next sequence container, I am using an SQL task to select all of the data from the staging table into a full result set named "objStaffActuals". Then I dropped a Foreach Loop Container into the sequence container and specified the enumerator has "Foreach ADO Enumerator" and set the ADO object source variable as the "objStaffActuals" with the enumeration mode as "Row in the first table". The staging table contains 5 columns - which I mapped as variables under Variable Mappings to match the procedure variables.

    Within the Foreach Loop Container - I dropped an SQL Task, used a project connection to connect to my database, and in the SQL statement I put: EXEC [dbo].SSIS_UpdateStaffingActuals ?,?,?,?,?,?,?,?. In Paramter Mapping - I mapped the variables to the correct data type in the procedure, order and input / output.

    And this works as you can see in the screenshot below - but it took 2:50 to execute 13 records. So this is good. But 2:50 to run the procedure 13 times is not good.

    I can run the same procedure from SSMS using values from the staging table and the procedure runs in less than 1 second.

    Any advice on what I need to look for to resolve why the SSIS package is running so painfully slow?

    Thanks,

  • August 21, 2015 at 6:17 am

    #1821651

    I don't have an answer for you, but I do have a question.

    Did you consider doing this in a dataflow?

    Dataflow source = (your query)

    Dateflow target = suitably configured OLEDB command.

    It seems a bit more direct than what you have done, though I'm guessing you had your reasons.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply