Open a script task or script component. Press F4 to open properties windows. You will find target .NET framework;
SSIS Project Target .NET Framework
Category: Data Design
All about Azure Databases, Azure single database, Azure managed instance, Azure Cosmos database, Azure VM based databases
Using SSIS Script task with Object variable and ADO Data Set
My input is a Report Date. I would like to create a collection object of DateTime using Script task with this input. There is no Data Flow in this work flow. Later, I will be using this collection object in a Foreach loop container.
The object type of a package variable is really ADO dataset. This is finished package;
Variables window
Task A – SQLT – Report Date Lookup
Task B – SCPT – Build Pending Report Dates
private static DataTable MakeDateDataTableFor(DateTime reportDate)
{
//create a new Data Table
DataTable table = new DataTable();
//Declare variables for DataColumn and DataRow objects
DataRow row;
DataColumn column;
//Create a new DataColumn and set it's data type
column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.ColumnName = "ReportDate";
column.ReadOnly = true;
table.Columns.Add(column);
//Create three new DataRow objects and add them to the DataTable
for (int i=0; i <=2; i++)
{
row = table.NewRow();
row["ReportDate"] = reportDate.AddDays(i);
table.Rows.Add(row);
}
return table;
}
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
// TODO: Add your code here
var reportDate = (DateTime)Dts.Variables["User::ReportDate"].Value;
//Create ADO Data Table object
DataTable table = MakeDateDataTableFor(reportDate);
Dts.Variables["User::ReportDateCollection"].Value = table;
Dts.TaskResult = (int)ScriptResults.Success;
}Task C – FELoop – Data processing for each date
Task D – SCPT – Show pending dates
public void Main()
{
// TODO: Add your code here
MessageBox.Show(Dts.Variables["User::ReportStartDate"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}Resources
https://www.blogger.com/blog/post/edit/2414567817322296815/3075025082999390053
You can read more about Data Tables here;
https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?redirectedfrom=MSDN&view=net-5.0
Continue Foreach loop after task failure in SSIS
I would like to continue a Foreach loop container even if a task, for example, script task fails. Here is a quick fix;
Press F4, this will bring up Properties window. Change “ForceExecutionResult” property to “Success”.
Foreach loop will continue even if script task inside fails.
The recommended way to get a good error message from script task, decorate them with try/catch block. Here is an example;
try
{
var x = 1/0;
}
catch (Exception ex)
{
var message = $"my error. ERR: {ex.Message.ToString()}";
Dts.Events.FireError(1, ex.TargetSite.ToString(), message, "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}These are good references on the topic;
https://richardswinbank.net/ssis/errors_in_script_tasks_and_components
Cross Apply and Outer Apply
Microsoft has introduced APPLY operator in SQL Server 2005. It allows joining between two table expressions, for example joining left/outer table expression with right/inner table expression. The way it works is that we have a table-valued expression on the right side and we want this table-valued expression to be evaluated for each row from the left table expression.
An ideal use case would be where we are unable to form any kind a join between two tables. For example a project table and calendar table.
(Left Side Table – Project Table)
ProjectKey ProjectEndDate
XX-ABC10 2018-10-31
XX-ABC11 2018-11-30
XX-ABD12 2019-01-31
XX-ABC13
(Right Side Table – Calendar Table)
FiscalQuarterStartDate FiscalQuarterEndDate
2018-10-01 2018-12-31
2019-01-01 2019-03-31
Each ProjectEndDate falls between a FiscalQuarter. We need to append a new column in our result set that will be FiscalQuarterEndDate.
Here is the query;
SELECT p.ProjectKey, p.ProjectEndDate, dates.FiscalQuarterEndDate
FROM Project p
CROSS APPLY
(
SELECT FiscalQuarterEndDate
FROM FiscalCalendar calendar
WHERE 1=1
AND p.ProjectEndDate
BETWEEN calendar.FiscalQuarterStartDate AND calendar.FiscalQuarterEndDate
) dates
WHERE 1=1
Here is the result;
ProjectKey ProjectEndDate FiscalQuarterEndDate
XX-ABC10 2018-10-31 2018-12-31
XX-ABC11 2018-11-30 2018-12-31
XX-ABD12 2019-01-31 2019-03-31
If we look at the result set, it returns only those rows that matches with the right table expression. Project Number XX-ABC13 is missing. CROSS APPLY is equivalent to an INNER JOIN. To be more precise its like a CROSS JOIN with a correlated sub-query).
If we want to return all rows from the left side then we need to apply OUTER APPLY which is equivalent to a LEFT OUTER JOIN.
Resources
https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/
C# component to read Excel data using OleDb in SSIS
This is how we can read data from Microsoft Excel using OleDb in SSIS;
public override void CreateNewOutputRows()
{
//Change this to your filename you do not need a connection manager
string fileName = @"E:\SFTP\RSS\Results.xlsx";
string SheetName = "Sheet1";
string cstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
using (System.Data.OleDb.OleDbConnection xlConn = new System.Data.OleDb.OleDbConnection(cstr))
{
xlConn.Open();
System.Data.OleDb.OleDbCommand xlCmd = xlConn.CreateCommand();
xlCmd.CommandText = "Select * from [" + SheetName + "$]"; //I assume this is the data you want
xlCmd.CommandType = CommandType.Text;
using (System.Data.OleDb.OleDbDataReader rdr = xlCmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 4; i < rdr.FieldCount; i++) //loop from 5th column to last
{
//The first 4 columns are static and added to every row
Output0Buffer.AddRow();
Output0Buffer.UniqueID = Int32.Parse(rdr[0].ToString());
Output0Buffer.Year = Int32.Parse(rdr[1].ToString());
Output0Buffer.ReportingWave = rdr.GetString(2);
Output0Buffer.SubmissionDate = rdr.GetString(3);
Output0Buffer.Question = rdr.GetName(i);
Output0Buffer.Answer = rdr.GetString(i);
}
}
}
xlConn.Close();
}
}We need to add output columns with correct data type. There is no need to setup connection manager because we are using it in the code.
This code will successfully import a file that looks like this;
My recent experience with Excel Sheet.
Check the data type of the column. If the type is “General”, then ACE will determine data type based on first 8-10 rows. If those are numeric then it might ignore alpha-numeric data in the column. Try to change column data type to “Text” and see what happens. It will work. You would be able to see numeric and non-numeric data in your data pipe line.
Resources;