We help IT Professionals succeed at work.
Get Started

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

rss2
rss2 asked
on
9,857 Views
Last Modified: 2013-11-10
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
Comment
Watch Question
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE