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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Mark WillsTopic AdvisorCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Reza RadConsultant, TrainerCommented:
explain more about what you want exactly? why you need scripting in SSIS?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.