SSIS Script task in C# - Building a datatable to then insert into a SQL table

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
rss2Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
0
 
nmcdermaidCommented:
Why not just import directly specifying pipe as the delimiter? Are there a variable number of columns per row? Can you post a sample of your input file. So far it appears that you don't need to use a script task at all.
0
 
Reza RadConsultant, TrainerCommented:
explain more about what you want exactly? why you need scripting in SSIS?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.