Tag: SSIS

The SSIS Object variable is a generic object, but I have never seen it used as anything other than a dataset — which is the default behavior that is accessible to you when you dump records into the SSIS object type variable — the first table in the dataset object will contain your records.

If you want to see the values inside the object in debug mode, you will need to cast it as something in order to see anything, for example, in a script task, if you cast the SSIS object variable to a dataset, you can then debug into the script to look at it’s content and structure. Similarly, the foreach enumerator is casting the object as a dataset and you access the first tables columns and you can debug and see row by row the values in the set.

DataSet ds = Dts.Variables["User::vObjectList"].Value as DataSet;
foreach (DataTable tbl in ds.Tables)
{
   foreach(DataRow row in tbl.Rows)
   {
       foreach (DataColumn column in tbl.Columns)
       {
            MessageBox.Show(row[column].ToString());
       }
   }
}

Some more examples;

Here are a couple of examples to demonstrate what the mysterious object should be cast to in order to further explore it in .NET.

ADO.NET (using a System.Data.DataSet):

DataSet ds = (DataSet)Dts.Variables["obj"].Value;
MessageBox.Show(ds.Tables[0].Rows.Count.ToString());

OLE DB:

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Dts.Variables["obj"].Value);
MessageBox.Show(dt.Rows.Count.ToString());

This is how;

Using(SqlConnection conn = (SqlConnection)Dts.Connections["AdoNet"].AcquireConnection(Dts.Transaction)){

if (conn.State != ConnectionState.Open){
 conn.Open();}

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = queryString;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(myDataTable);
}

Resource;

https://stackoverflow.com/questions/41733531/ssis-script-task-connecting-the-ado-net-and-populating-datatable

Define two variables; ExcelFile –> String and ExcelTables –> Object. Drop a script task on designer surface;

Here is the script;

public void Main()
{
            string excelFile;
            string connectionString;
            OleDbConnection excelConnection;
            DataTable tablesInFile;
            int tableCount = 0;
            string currentTable;
            int tableIndex = 0;

            //string[] excelTables = new string[5];
            string[] excelTables = new string[20];

            excelFile = Dts.Variables["ExcelFile"].Value.ToString();
            connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFile};Extended Properties=Excel 12.0";
            excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");
            tableCount = tablesInFile.Rows.Count;

            foreach (DataRow tableInFile in tablesInFile.Rows)
            {
                currentTable = tableInFile["TABLE_NAME"].ToString();
                char lastCharacter = currentTable[currentTable.Length - 2];
                if (lastCharacter == '$')
                {
                    excelTables[tableIndex] = currentTable;
                    tableIndex += 1;
                }
            }

            Dts.Variables["ExcelTables"].Value = excelTables;

            Dts.TaskResult = (int)ScriptResults.Success;
}

You can display object variables values using this;

string[] tablesInFile = (string[])Dts.Variables["ExcelTables"].Value;
            foreach (string tableInFile in tablesInFile)
            {
                results += " " + tableInFile + EOL;
            }

            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);

This is a short how-to convert a string to byte-array without any encoding (byte-by-byte) in SQL Server Integration Services (SSIS).

I would like to save this string “I love you” as BLOB. Let’s assume our Output column name is fileContent.

Row.filecontent.AddBlobData(GetBytes("I love you"));

AND

byte[] GetBytes(string str)
{
    byte[] bytes = new byte[str.Length * sizeof(char)];
    System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
    return bytes;
}

Reference

https://stackoverflow.com/questions/35703025/how-do-i-convert-a-string-to-a-blobcolumn-in-ssis/46837584

https://stackoverflow.com/questions/472906/how-do-i-get-a-consistent-byte-representation-of-strings-in-c-sharp-without-manu/10380166#10380166