none
Packages run faster on BIDS and slower on Agent Job

    Question

  • Hi,
    When running my packages through in BIDS, take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time. If I disable the task script (simple script), it run faster.

    Anybody knows why this happen ? Do I need to tune up something ?


    Any input in this will sincerely be appreciated.


    Thiago Faria

    Wednesday, December 11, 2013 11:25 PM

All replies

  • Hi Thiago

    in general this is caused by heavy loads on the prod machine

    but sharing particulars as to what the script does may help better.


    Arthur My Blog

    Thursday, December 12, 2013 3:40 PM
  • what is the functionality of the script task in your package?

    Thanks, hsbal

    Thursday, December 12, 2013 6:43 PM
  •        
    The class read the number of lines in file and return this value. 

    The file is on the same SQL server.



    The code used:

     public void Main()
            {

                string ffConnection = (string)(Dts.Connections["Arquivos LAPAs"].AcquireConnection(null) as String);
                FileInfo flatFileInfo = new FileInfo(ffConnection);
                long fileSize = flatFileInfo.Length;
                if (fileSize > 0)
                {
                    int lineCount = 0;
                    StreamReader fsFlatFile = new StreamReader(ffConnection);
                    while (!(fsFlatFile.EndOfStream))
                    {
                        Console.WriteLine(fsFlatFile.ReadLine());
                        lineCount += 1;
                        Dts.Variables["var_qtdLinhasArquivoInicio"].Value = lineCount;
                    }
                }
                else
                {
                    Dts.Variables["var_arquivoVazio"].Value = true;
                    Dts.Variables["var_qtdLinhasArquivoInicio"].Value = 0;
                }
                Dts.TaskResult = (int)ScriptResults.Success;


            }

    Thiago Faria


    • Edited by JamesWest Thursday, December 12, 2013 7:09 PM
    Thursday, December 12, 2013 7:08 PM
  • Why would you read a file with string ffConnection = (string)(Dts.Connections["Arquivos LAPAs"].AcquireConnection(null) as String); ?

    I recommend you use the stock Flat File Connection manager with a Script Transformation component that is not blocking to count the lines in the file. Or use the http://www.sqlis.com/post/Row-Number-Transformation.aspx


    Arthur My Blog

    Thursday, December 12, 2013 7:13 PM
  • I use this code for know the quantity of real rows in file. This variable will go receive the file path and your name. I need to compare number of rows in file versus rows inserted in table.

    Thiago Faria

    Wednesday, December 18, 2013 6:25 PM
  • I suggest you replace the script with the Row Count Transformation:


    Arthur My Blog

    Wednesday, December 18, 2013 7:07 PM