Month: August 2021

Dataset and Data Tables (a refresher)

Dataset is an in-memory representation of a database relationship. Data Tables are individual tables that can be joined with Data relation objects.

Let’s go through an example of a tenant and his/her maintenance request;

Create a Dataset;

//create dataset
DataSet serviceRequest = new DataSet();

Create tenant table

//create tenant table
DataTable tenant = new DataTable();
tenant.Clear();
//add columns
tenant.TableName = "tenant";
tenant.Columns.Add("tenantID");
tenant.Columns.Add("Name");
tenant.Columns.Add("AptNumber");
//add rows
DataRow rowT = tenant.NewRow();
rowT["tenantID"] = "A1";
rowT["Name"] = "khan";
rowT["AptNumber"] = "1";
tenant.Rows.Add(rowT);

//Add this table to data set
serviceRequest.Tables.Add(tenant);

Create request table

//create request table
DataTable request = new DataTable();
request.Clear();
//add columns
request.TableName = "request";
request.Columns.Add("requestID");
request.Columns.Add("tenantID");
request.Columns.Add("description");
//add rows
DataRow rowR = request.NewRow();
rowR["requestID"] = "1";
rowR["tenantID"] = "A1";
rowR["description"] = "air conditioner does not work";
request.Rows.Add(rowR);
//add this table to data set
serviceRequest.Tables.Add(request);

Create relationship

//create relationship
DataRelation relation;
DataColumn tenantColumn = serviceRequest.Tables["tenant"].Columns["tenantID"];
DataColumn requestColumn = serviceRequest.Tables["request"].Columns["tenantID"];
relation = new DataRelation("relation", tenantColumn, requestColumn);
//assign relation
serviceRequest.Relations.Add(relation);

Create LINQ query to read data;

//simple LINQ query
var maintenanceData = (from x in serviceRequest.Tables["tenant"].AsEnumerable()
join y in serviceRequest.Tables["request"].AsEnumerable()
on x.Field<string>("tenantID") equals y.Field<string>("tenantID")
select new
   {
       TenantID = x.Field<string>("tenantID"),
       RequestID = y.Field<string>("requestID"),
       Description = y.Field<string>("description")
   }).ToList();

Here is the output of this LINQ query;

If we are going to add a new column in request table, for example AllowToEnterApt. We can use foreach loop to update the values in this column;

serviceRequest.Tables["request"].Columns.Add("AllowToEnterApt");
foreach (DataRow row in serviceRequest.Tables["request"].Rows)
{
     row["AllowToEnterApt"] = 1;
}

Remove single quote from strings

I would like to remove single quote from beginning and end of this string;

string tabName = "'i# milestones$'";
string outputTabName = tabName.Trim(new char[]{(char)39});

Where (char)39 represents ', and the .Trim() will remove the first and last ' from the string; You can try like this as well:

string outputTabName = tabName.Trim('\'');

Get File Extension using T-SQL

The easies way to do this using SQL Server 2017 and up;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	--check there is a '.' in ExcelFileName
	CASE WHEN @FileName LIKE '%.%' THEN
		REVERSE(left(REVERSE(@FileName), CHARINDEX('.', REVERSE(@FileName)) - 1))
	ELSE ''
	END ExcelFileExtension
WHERE 1=1

Earlier versions can use this approach;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	CASE 
         WHEN @FileName LIKE '%.%' THEN 
			RIGHT(@FileName, LEN(@FileName) - CHARINDEX('.', @FileName)) 
         ELSE '' 
       END ExcelFileExtension 
WHERE 1=1

Object Variable conversion to Table or List

I would like to get data out of Object variable in Script task or Script component. My object variable name is VersionList;

If we have used OLE DB to load data into Object variable, we need to use this;

// Set up the DataAdapter to extract the data, and the DataTable object to capture those results. This will write the contents of the Recordset to a new DataTable object.
// Create the connector
OleDbDataAdapter da = new OleDbDataAdapter();       

// Create the output DataTable
DataTable dt = new DataTable();                     

// Load the DataTable
da.Fill(dt, Dts.Variables["VersionList"].Value);       

// Now, replace the value in the VersionList variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset

Dts.Variables["VersionList"].Value = dt;

If we have used ADO.NET to load data into Object, then we need to use this;

 try
            {
                var ds = Dts.Variables["User::VersionList"].Value as DataSet;
                DataTable dt = new DataTable();                     
                // Create the output DataTable
                dt.TableName = "VersionList";
                // Now, replace the value in the VersionList object variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset
                dt = ds.Tables[0];
                Dts.Variables["User::VersionList"].Value = dt;

                Dts.TaskResult = (int)ScriptResults.Success;

            }
            catch (Exception ex)
            {
                var message = $"Error comparing version numbers. ERR: {ex.Message.ToString()}";
                //MessageBox.Show(message);
                Dts.Events.FireError(1, ex.TargetSite.ToString(), message, "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

We can use it in script component like this;

//get version and rules from VersionList variable
DataTable dt = (DataTable)Variables.VersionList;
//iterate over rows
foreach (DataRow row in dt.Rows)
{
   string message = $@"Details: {row["VersionNumber"].ToString()} - {row["VersionCode"].ToString()}
                           - {row["TabName"].ToString()} - {row["CellRangeCode"].ToString()} 
                            - {row["CellRange"].ToString()}";
   MessageBox.Show(message);
}

If for some reasons we are using Foreach loop and keep referring to same record set, then best design is to keep it ouside Foreach loop. An example is this;

We are getting VersionList from (1). We convert it to a data table in (2). We use it inside data flow task (3) of Foreach loop container.