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

        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)
                        DataRow dr_ = dt_.NewRow();
                        dr_[0] = value;
                        dr_[1] = string.Empty;

            // check the contents of the new datatable dt_
            foreach (DataRow drr in dt_.Rows)
                foreach (DataColumn dcc in dt_.Columns)
            //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!!

Who is Participating?
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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.
Reza RadConsultant, TrainerCommented:
explain more about what you want exactly? why you need scripting in SSIS?
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.