November 4, 2015 at 9:36 am
hi,
I have a script task that reads the variable named"result" ( the stored procedure is ran in execute sql task and stores the result in this variable) and writes the data from the variable to a file. It runs fine for some dates and it hangs there , if there is more data.
Hoe can I make it run faster?
Here is the code in my script task.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
namespace ST_b1d96bfded9a46e58b4c8ac415aeaed9.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
// TODO: Add your code here
OleDbDataAdapter oleDA = new OleDbDataAdapter();
Dts.TaskResult = (int)ScriptResults.Success;
string path = @"C:\Data\Data_Extract_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".csv";
if (Dts.TaskResult == 0)
{
if (!System.IO.File.Exists(path))
System.IO.File.Create(path);
DataTable dT = new DataTable();
try
{
//dT = Dts.Variables["User::Result"].Value as DataTable;
oleDA.Fill(dT, Dts.Variables["User::Result"].Value);
string Row = "";
for (int i = 0; i < dT.Rows.Count; i++)
{
for (int j = 0; j < dT.Columns.Count; j++)
{
Row += dT.Rows[j].ToString();
if (j != dT.Columns.Count - 1)
Row += "|";
}
Row += "";
}
System.IO.File.WriteAllText(path, Row);
}
catch (Exception Ex)
{
System.IO.File.WriteAllText(path, Ex.StackTrace + "" + Ex.Message);
}
}
}
}
}
November 4, 2015 at 9:51 am
komal145 (11/4/2015)
hi,I have a script task that reads the variable named"result" ( the stored procedure is ran in execute sql task and stores the result in this variable) and writes the data from the variable to a file. It runs fine for some dates and it hangs there , if there is more data.
Hoe can I make it run faster?
Here is the code in my script task.
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
namespace ST_b1d96bfded9a46e58b4c8ac415aeaed9.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
public void Main()
{
// TODO: Add your code here
OleDbDataAdapter oleDA = new OleDbDataAdapter();
Dts.TaskResult = (int)ScriptResults.Success;
string path = @"C:\Data\Data_Extract_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".csv";
if (Dts.TaskResult == 0)
{
if (!System.IO.File.Exists(path))
System.IO.File.Create(path);
DataTable dT = new DataTable();
try
{
//dT = Dts.Variables["User::Result"].Value as DataTable;
oleDA.Fill(dT, Dts.Variables["User::Result"].Value);
string Row = "";
for (int i = 0; i < dT.Rows.Count; i++)
{
for (int j = 0; j < dT.Columns.Count; j++)
{
Row += dT.Rows[j].ToString();
if (j != dT.Columns.Count - 1)
Row += "|";
}
Row += "";
}
System.IO.File.WriteAllText(path, Row);
}
catch (Exception Ex)
{
System.IO.File.WriteAllText(path, Ex.StackTrace + "" + Ex.Message);
}
}
}
}
}
You are not making the best use of SSIS by working this way.
This task can be accomplished by adding a single data flow. The source for your data flow will be the stored proc and the destination will be the file you wish to create. No code is required and, assuming your proc runs reasonably quickly, speed should not be an issue.
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