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.
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);
lineCount += 1;
Dts.Variables["var_qtdLinhasArquivoInicio"].Value = lineCount;
Dts.Variables["var_arquivoVazio"].Value = true;
Dts.Variables["var_qtdLinhasArquivoInicio"].Value = 0;
Dts.TaskResult = (int)ScriptResults.Success;
- Edited by JamesWest Thursday, December 12, 2013 7:09 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
- Proposed as answer by Mike YinMicrosoft contingent staff, Moderator Monday, December 16, 2013 7:22 AM