Friday, 27 September 2013

Dynamically creating SSIS packages

Introduction: According to an old adage:

'If you want to write a code then you should write a code that will write your code instead.'

For a SSIS developer it's a fairy tell, if he can create 100 of packages just clicking a button. Yes this fairy tell can come true and I will show you how.

Lot of times in real working scenario it happens like a developer need to create same kind of packages for number tables. Initially it's interesting but the interest goes down gradually over the period of time due to repeating work. Lack of interest leads to manual error and rework. The package creation should be automated to avoid such unwanted situations.

Technology Required: C# and Knowledge on SSIS.

The DLLS used over here are SQL Server related DLLS. These DLLs will only be present if you have SQL Server installed on your system.

Reading a package present in file system:

using Microsoft.SqlServer.Dts.Runtime;
public Microsoft.SqlServer.Dts.Runtime.Package ReadPackage(string packagePath)

{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();

Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

package = app.LoadPackage(packagePath, null);

return package;


}


Modifying SSIS OLEDB Connection manager dynamically:

Code:

using Microsoft.SqlServer.Dts.Runtime; 
public Microsoft.SqlServer.Dts.Runtime.Package modifyOLEDBConnection(Microsoft.SqlServer.Dts.Runtime.Package package, string oldConName, string newConName, string connectionString)
{
ConnectionManager conmgr = package.Connections[oldConName];
conmgr.Name = newConName;
conmgr.ConnectionString = connectionString;// Global.SSISConnectionString;
return package;
}

 
Modifying SSIS Configurations:

using Microsoft.SqlServer.Dts.Runtime;

public void modifyConfig(Package package, string oldName, string name, DTSConfigurationType type, string setting, string target)
{
Configurations configurations = package.Configurations;
Microsoft.SqlServer.Dts.Runtime.Configuration configuration;
 
configuration = configurations[oldName];
configuration.Name = name;
configuration.ConfigurationType = type;
configuration.ConfigurationString = setting;
configuration.PackagePath = target;
}
 



Modifying Union All:


using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
 
public IDTSComponentMetaData100 dfModifyUnionAllMapping(IDTSComponentMetaData100 unionAll,int inputIndex,string inputColumnName,string outputColumnName)
{
IDTSInput100 destInput = unionAll.InputCollection[inputIndex];
IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
CManagedComponentWrapper instanceUnionAll = unionAll.Instantiate();
 
int inputColumnLen=0;

foreach (IDTSVirtualInputColumn100 vColumn in destVirInput.VirtualInputColumnCollection)
{
            

if (vColumn.Name.ToUpper() == inputColumnName.ToUpper())
{
instanceUnionAll.SetUsageType(
destInput.ID, destVirInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

inputColumnLen=vColumn.Length;
}
}
IDTSInputColumn100 inputCol = null;
for (int i = 0; i < unionAll.InputCollection[inputIndex].InputColumnCollection.Count; i++)
{
 
if (unionAll.InputCollection[inputIndex].InputColumnCollection[i].Name.ToUpper() == inputColumnName.ToUpper())
inputCol = unionAll.InputCollection[inputIndex].InputColumnCollection[i];

}

int outputLID = 0;

for (int i = 0; i < unionAll.OutputCollection[0].OutputColumnCollection.Count; i++)
{
 
if (unionAll.OutputCollection[0].OutputColumnCollection[i].Name.ToUpper() == outputColumnName.ToUpper())
{
outputLID = unionAll.OutputCollection[0].OutputColumnCollection[i].LineageID;
 
if (unionAll.OutputCollection[0].OutputColumnCollection[i].Length < inputColumnLen)
unionAll.OutputCollection[0].OutputColumnCollection[i].SetDataTypeProperties(unionAll.OutputCollection[0].OutputColumnCollection[i].DataType, inputColumnLen, unionAll.OutputCollection[0].OutputColumnCollection[i].Precision, unionAll.OutputCollection[0].OutputColumnCollection[i].Scale, unionAll.OutputCollection[0].OutputColumnCollection[i].CodePage);
}           

}
 
for (int i = 0; i < unionAll.InputCollection[inputIndex].InputColumnCollection.Count; i++)
{
 
if (unionAll.InputCollection[inputIndex].InputColumnCollection[i].CustomPropertyCollection[0].Value == outputLID)
unionAll.InputCollection[inputIndex].InputColumnCollection.RemoveObjectByID(unionAll.InputCollection[inputIndex].InputColumnCollection[i].ID);
 
if (unionAll.InputCollection[inputIndex].InputColumnCollection[i].Name.ToUpper() == inputColumnName.ToUpper())

unionAll.InputCollection[inputIndex].InputColumnCollection[i].CustomPropertyCollection[0].Value = outputLID;

}

return unionAll;
}



Modifying Execute SQL Task:

using System.Threading.Tasks;
public Package EditExecuteSQL(Package package,string componentName,string query)

{
Executable executeSql = package.Executables[componentName];
 
Microsoft.SqlServer.Dts.Runtime.TaskHost taskhostexecuteSql = executeSql as Microsoft.SqlServer.Dts.Runtime.TaskHost;
taskhostexecuteSql.Properties["SqlStatementSource"].SetValue(taskhostexecuteSql, query);
return package;

}


Create Flat file connection:

public Package CreateFlatFileConnection(Microsoft.SqlServer.Dts.Runtime.Package package, string strConName, string strConnectionString, string strSchemaTabName)
{
try
{
DataTable dt = da.GetColumnsandDataType(strSchemaTabName,strConnectionString);
ConnectionManager conmgr;
conmgr = package.Connections.Add("FLATFILE");
conmgr.Name = strConName;
 
//Setting the Unicode, CodePage and Connection string properties of the Connection manager

conmgr.Properties["Unicode"].SetValue(conmgr, true);
conmgr.Properties["CodePage"].SetValue(conmgr, true);


conmgr.ConnectionString = strConnectionString;
 
 
string strDestTabName = "";

DtsProperties connProperties = conmgr.Properties;
 
/* Setting FlatFile Properites*/
const string quote = "\"";

//Removing schema part from the table

string strDestTabName = strSchemaTabName.Substring(strSchemaTabName.IndexOf(".") + 1, strSchemaTabName.Length - strSchemaTabName.IndexOf(".") - 1);


string str1 = "@[User::FilePath]+" + quote + "\\\\" + Global.sourceSystem + strDestTabName + Global.destinationSystem + quote + "+ replace(replace(replace((DT_WSTR,30) (DT_DBTIMESTAMP) @[System::StartTime]," + quote + "-" + quote + "," + quote + " " + quote + ")," + quote + ":" + quote + "," + quote + " " + quote + ")," + quote + " " + quote + "," + quote + "" + quote + ")+" + quote + ".dat" + quote;

//Setting the ColumnNamesInFirstDataRow property

conmgr.Properties["ColumnNamesInFirstDataRow"].SetValue(conmgr, false);

//Setting the expression for the Connection String property

conmgr.Properties["ConnectionString"].SetExpression(conmgr, str1);
conmgr.AcquireConnection(null);

/*Getting the Output Column*/

if (conmgr.InnerObject != null)

{
 
RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFileDestionation = conmgr.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

int i = 0;

//RuntimeWrapper.DataType

foreach (DataRow row in dt.Rows)

{
 
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol = connectionFlatFileDestionation.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;

RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;

/*Setting Column Properties*/

columnName.Name = row["ColumnName"].ToString();

if (i == dt.Rows.Count - 1)

connectionFlatFileDestionation.Columns[i].ColumnDelimiter = "\r\n";

else

connectionFlatFileDestionation.Columns[i].ColumnDelimiter = "|";
connectionFlatFileDestionation.Columns[i].DataType = GetSSISDataType(row["TypeName"].ToString());
connectionFlatFileDestionation.Columns[i].TextQualified = true;
connectionFlatFileDestionation.Columns[i].ColumnType = "Delimited";
            

i++;

}                                   

}

}
 
catch (Exception ex)
{
 
//MessageBox.Show("Error in Creating Flatfile Connection or setting its properties");
}
 
return package;



}


Modify OLEDB Connection:

public IDTSComponentMetaData100 dfModifyOLEdbSource(Package package, IDTSComponentMetaData100 oledbSource, string connectionMgrName, string strSQLCommand, List<string> sortKeyPositions)
{
CManagedComponentWrapper instanceSource = oledbSource.Instantiate();
 
//setting Connection Manager Informations

if (oledbSource.RuntimeConnectionCollection.Count > 0)

{
oledbSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(package.Connections[connectionMgrName]);

oledbSource.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[connectionMgrName].ID;
}
 
//Setting OLEDB source properties

instanceSource.SetComponentProperty("AccessMode", 2);
instanceSource.SetComponentProperty("SqlCommand", strSQLCommand);
instanceSource.AcquireConnections(null);

instanceSource.ReinitializeMetaData();
instanceSource.ReleaseConnections();
 
//Setting sort key positions

for (int i = 0; i < sortKeyPositions.Count; i++)


{
 
for (int j = 0; j < oledbSource.OutputCollection[0].OutputColumnCollection.Count; j++)

{
 
if (oledbSource.OutputCollection[0].OutputColumnCollection[j].Name == sortKeyPositions[i])
oledbSource.OutputCollection[0].OutputColumnCollection[j].SortKeyPosition = i + 1;
}
}
return oledbSource;
}