Hi Experts,
Could you help me with this c# script I'm writing for an SSIS package? All I want to do is parse the rows within an object I've pulled in, and put the nice-and-clean, normalised rows into a SQL table.
I pulled in a flat file that has rows of data connected with "|" between the data items into a sql staging table.
I pulled in the rows of that staging table into an object variable in the SSIS package.
I am now writing a script (c#) that reads the rows from that object variable and parses out the "|" of each data item so I can put the cleaned up data rows into another SQL table. (It's this last bit that I'm having trouble with..)
Here is my script so far:
(reading in dsReplyStaging object variable)
(writing to dsParsedObject object variable)
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks;
using System.Xml;
using ADODB;
namespace ST_407d5e0470e74bc08fa1e10c9bff9833.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
};
#endregion
public void Main()
{
OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
DataTable dt_ = new DataTable();
String strRow;
oleDA.Fill(dt, Dts.Variables["dsReplyStaging"].Value);
//oleDA.Fill(dt_, Dts.Variables["dsParsedObject"].Value);
string[] months= new string[]{"January","February"};
foreach (DataRow dr in dt.Rows) {
foreach (DataColumn dc in dt.Columns)
{
strRow = dr[dc].ToString();
string[] values = strRow.Split('|');
foreach (string value in values)
{
//MessageBox.Show(value);
DataRow dr_ = dt_.NewRow();
dr_[0] = value;
dr_[1] = string.Empty;
dt_.Rows.Add(dr_);
}
}
}
// check the contents of the new datatable dt_
foreach (DataRow drr in dt_.Rows)
{
foreach (DataColumn dcc in dt_.Columns)
{
MessageBox.Show(drr[dcc].ToString());
}
}
//Dts.Variables["dsParsedObject"].Value = ConvertToRecordset(dt_);
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
My question is, how do I get each value that I've split up into the SQL table???
Muchos gracias!!
rss2