SQL Server integration services environment setup

These are required steps;

  1. SQL Server Integration Services

Install Integration Services

  1. Create SSIS DB Catalog using SQL Server Management Studio

Create and Configure SSIS Catalog

  1. Microsoft Access Database Engine 2016 Redistributable (ACE driver for Excel Files)

Install Microsoft Access Database Engine 2016 Redistributable

  1. A domain account that has read/write/delete permission to file system.

This will be used as a proxy account. This is required because SSIS Packages will run from SQL Server Agent and the job will fail because of security context.

Counting elements in arrays and List

String array elements can be counted like this;

string[] empty = new string[5];
var totalCount = empty.Count(); //5

string[] flower = {"Red Rose", "White Rose", "Yellow Rose", "Clover", "Cameilla"};

totalCount = flower.Count(); //5
var cloverCount = flower.Count(x => x == "Clover");

List elements can be counted like this;

var flower = new List<string>() {"Red Rose", "White Rose", "Yellow Rose", "Clover", "Cameilla" };
var count = flower.Count();
var listCount = flower.Count(x => x.Contains("Clover"));

and if you have a custom class, do this;

flower.count(x => x.flowerName == "Clover");

SSIS: Object variable

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());

SSIS: Script task for connecting ADO.NET and Populating Data Table

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

KeyValue pair class

The KeyValue pair class stores a pair of values in a single list.

It’s super easy to create a list of single value. Here is an example;

List<string> firstList =  new List<string> {"'cover page$'", "'i# milestones$'", "'ii# tasks$'" };
List<string> secondList = new List<string> { "'cover page$'", "'i# milestones$'", "'ii# tasks$'" };
var exceptList = secondList.Except(firstList);
Console.WriteLine($"\nsingle string: Value in second list that are not in first List");
foreach (var val in exceptList)
{
     Console.WriteLine($"single string: {val}");
}

What if we want to store pair of values instead of creating any custom classes? We can use KeyValue pair class;

var parentList = new List<KeyValuePair<string, string>>()
{
    new KeyValuePair<string, string>("v2-2021", "'cover page$'"),
    new KeyValuePair<string, string>("v2-2021", "'i# milestones$'"),
    new KeyValuePair<string, string>("v2-2021", "'ii# tasks$'"),
    new KeyValuePair<string, string>("v2-2021", "'iii# spendplan$'"),
};
var parentSubList = new List<KeyValuePair<string, string>>()
{
    new KeyValuePair<string, string>("v2-2021", "'cover page$'"),
    new KeyValuePair<string, string>("v2-2021", "'i# milestones$'"),
    new KeyValuePair<string, string>("v2-2021", "'ii# tasks$'"),
};
var exceptList1 = parentSubList.Except(parentList);
Console.WriteLine($"\nparentSubList->parentList: Value in second list that are not in first List");
foreach (var val in exceptList1)
{
    Console.WriteLine($"{val}");
}
IsASubset = parentSubList.All(i => parentList.Contains(i));
Console.WriteLine($"\nparentSubList->parentList: all members of subset (parentSubList) exists in list1 (parentList): {IsASubset}");
}

KeyValue pair class can also be used like this;

var myList = new List<KeyValuePair<string, string>>();
//add elements now
myList.Add(new KeyValuePair<string, string>("v2-2021", "'cover page$'"));
myList.Add(new KeyValuePair<string, string>("v2-2021", "'i# milestones$'"));
myList.Add(new KeyValuePair<string, string>("v2-2021", "'ii# tasks$'"));
foreach (var val in myList)
{
    Console.WriteLine($"Another style: {val}");
}

LINQ methods, for example Except can be used without implementing any Comparer classes.