Abstract
This article explains how to create an SSIS package with both Data Flow Task and Conditional Split Transformation connected together using C# programming language.
Requirements
- Microsoft Visual Studio 2008 (or later editions)
- SQL Server 2005 (or later editions)
- AdventureWorks2008R2 database (downloadable database file available here)
Article
If the above requirements are all met, we will begin by launching the Microsoft Visual Studio edition.
Create a new project Integration Services Project which is located under Business Intelligence Projects.
After you have named the new project, proceed to click and drag the script task in the Control Flow pane of the new package.
Right-click the script task and click on “Edit”
Under the Script Task Editor change the “ScriptLanguage” to “Microsoft Visual C# 2008”.
In Project Explorer import relevant references and ensure that you have declared namespaces as below:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts.Tasks;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
After declarations, create an instance of the application and package:
Application SIFISO_app = new Application();
Package dyna_pkg = new Package();
Insert source and destination connections:
ConnectionManager cm = p.Connections.Add(\”OLEDB\”);
cm.Name = \”OLEDB SOURCE\”;
cm.ConnectionString = string.Format(
\”Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;\”, \”your_Source_SQL_Server\”, \”AdventureWorks\”);
ConnectionManager cm_DES = p.Connections.Add(\”OLEDB\”);
cm_DES.Name = \”OLEDB DESTINATION\”;
cm_DES.ConnectionString = string.Format(
\”Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;\”, \”your_Destination_SQL_Server\”, \”AdventureWorks\”);
Insert a data flow task onto a package:
Executable e = p.Executables.Add(\”STOCK:PipelineTask\”);
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
thMainPipe.Name = “selectSIFISO data Flow”;
Declare source components of the Data Flow Task:
IDTSComponentMetaData100 component = dataFlowTask.ComponentMetaDataCollection.New();
component.Name = “OLEDBSource”;
component.ComponentClassID = “DTSAdapter.OleDbSource.2”;
Initialize the source component:
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
Assign connection manager to data flow task source component:
component.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm);
The rest of the data flow task source component properties should be as follows:
instance.SetComponentProperty(“AccessMode”, 2);
instance.SetComponentProperty(“SqlCommand”, “SELECT * FROM [AdventureWorks].[HumanResources].[Employee]”);
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
Insert the Conditional Split Transformation component onto the data flow task:
IDTSComponentMetaData100 dataConditional_Split = dataFlowTask.ComponentMetaDataCollection.New();
dataConditional_Split.ComponentClassID = “DTSTransform.ConditionalSplit”;
dataConditional_Split.Name = “Gender Column Split”;
dataConditional_Split.Description = “Evaluates and directs rows in a dataset based on Gender column.”;
CManagedComponentWrapper data_Split_Wrapper = dataConditional_Split.Instantiate();
data_Split_Wrapper.ProvideComponentProperties();
Connect the OLE DB source component to the Conditional Split Transformation component:
dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(component.OutputCollection[0],
dataConditional_Split.InputCollection[0]);
Acquire input column collections:
IDTSInput100 input = dataConditional_Split.InputCollection[0];
IDTSVirtualInput100 dataConvertVirtualInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns =
dataConvertVirtualInput.VirtualInputColumnCollection;
IDTSOutput100 dataConvertOutput = dataConditional_Split.OutputCollection[0];
IDTSOutputColumnCollection100 dataConvertOutputColumns = dataConvertOutput.OutputColumnCollection;
Declare local variables:
int sourceColumnLineageId;
string get_name = “”;
Map source component columns into the Conditional Split Transformation component:
foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumns)
{
get_name = virtualInputColumn.Name.ToString();
if (get_name == “Gender”)
{
sourceColumnLineageId = dataConvertVirtualInput.VirtualInputColumnCollection[get_name].LineageID;
data_Split_Wrapper.SetUsageType(input.ID, dataConvertVirtualInput, sourceColumnLineageId, DTSUsageType.UT_READONLY);
IDTSOutput100 splitOutput_1 = data_Split_Wrapper.InsertOutput(DTSInsertPlacement.IP_BEFORE, dataConditional_Split.OutputCollection[0].ID);
splitOutput_1.Name = “Case_Male”;
splitOutput_1.Description = “Accepts values where gender is emale”;
splitOutput_1.IsErrorOut = false;
IDTSOutput100 splitOutput_2 = data_Split_Wrapper.InsertOutput(DTSInsertPlacement.IP_BEFORE, dataConditional_Split.OutputCollection[1].ID);
splitOutput_2.Name = “Case_Female”;
splitOutput_2.Description = “Accepts values where gender is female”;
splitOutput_2.IsErrorOut = false;
data_Split_Wrapper.SetOutputProperty(splitOutput_1.ID,”EvaluationOrder”, 0);
data_Split_Wrapper.SetOutputProperty(splitOutput_1.ID, “FriendlyExpression”, “[Gender] == \\\”M\\\”\”);
data_Split_Wrapper.SetOutputProperty(splitOutput_2.ID, “EvaluationOrder”, 1);
data_Split_Wrapper.SetOutputProperty(splitOutput_2.ID, “FriendlyExpression”, \”[Gender] == \\\”F\\\”\”);
break;
}
}
Insert OLE DB Destination component for the first split condition:
IDTSComponentMetaData100 destinationSPLIT_1 = dataFlowTask.ComponentMetaDataCollection.New();
destinationSPLIT_1.ComponentClassID = \”DTSAdapter.OleDbDestination\”;
CManagedComponentWrapper destDesignTimeSPLIT_1 = destinationSPLIT_1.Instantiate();
destDesignTimeSPLIT_1.ProvideComponentProperties();
Assign connection manager to data flow task destination component:
destinationSPLIT_1.RuntimeConnectionCollection[0].ConnectionManagerID = cm_DES.ID;
destinationSPLIT_1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm_DES);
The rest of data flow task destination component properties should be as follows:
destDesignTimeSPLIT_1.SetComponentProperty(“OpenRowset”, “[HumanResources].[Employee_SIFISO]”);
destDesignTimeSPLIT_1.SetComponentProperty(“AccessMode”, 3);
destDesignTimeSPLIT_1.SetComponentProperty(“FastLoadOptions”, “TABLOCK,CHECK_CONSTRAINTS”);
Insert OLE DB Destination component for the second split condition:
IDTSComponentMetaData100 destinationSPLIT_2 = dataFlowTask.ComponentMetaDataCollection.New();
destinationSPLIT_2.ComponentClassID = \”DTSAdapter.OleDbDestination\”;
CManagedComponentWrapper destDesignTimeSPLIT_2 = destinationSPLIT_2.Instantiate();
destDesignTimeSPLIT_2.ProvideComponentProperties();
Assign connection manager to data flow task destination component:
destinationSPLIT_2.RuntimeConnectionCollection[0].ConnectionManagerID = cm_DES.ID;
destinationSPLIT_2.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm_DES);
destDesignTimeSPLIT_2.SetComponentProperty(“OpenRowset”, “[HumanResources].[Employee_SIFISO]”);
destDesignTimeSPLIT_2.SetComponentProperty(“AccessMode”, 3);
destDesignTimeSPLIT_2.SetComponentProperty(\”FastLoadOptions\”, \”TABLOCK,CHECK_CONSTRAINTS\”);
Connect the Conditional Split Transformation component onto the two OLEDB destination components:
IDTSPath100 pathSPLIT_1 = dataFlowTask.PathCollection.New();
pathSPLIT_1.AttachPathAndPropagateNotifications(dataConditional_Split.OutputCollection[0], destinationSPLIT_1.InputCollection[0]);
pathSPLIT_1.Name =”Case_Male”;
IDTSPath100 pathSPLIT_2 = dataFlowTask.PathCollection.New();
pathSPLIT_2.Name = “Case_Female”;
pathSPLIT_2.AttachPathAndPropagateNotifications(dataConditional_Split.OutputCollection[1], destinationSPLIT_2.InputCollection[0]);
Acquire input column collections of the two OLEDB destination components:
IDTSInput100 destinationInputerrSPLIT_1 = destinationSPLIT_1.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInputerrSPLIT_1 = destinationInputerrSPLIT_1.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumnserrSPLIT_1 = destinationVirtualInputerrSPLIT_1.VirtualInputColumnCollection;
IDTSInput100 destinationInputerrSPLIT_2 = destinationSPLIT_2.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInputerrSPLIT_2 = destinationInputerrSPLIT_2.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumnserrSPLIT_2 = destinationVirtualInputerrSPLIT_2.VirtualInputColumnCollection;
destDesignTimeSPLIT_1.AcquireConnections(null);
destDesignTimeSPLIT_1.ReinitializeMetaData();
destDesignTimeSPLIT_1.ReleaseConnections();
destDesignTimeSPLIT_2.AcquireConnections(null);
destDesignTimeSPLIT_2.ReinitializeMetaData();
destDesignTimeSPLIT_2.ReleaseConnections();
Map Conditional Split Transformation component columns to the two OLEDB destination components:
foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumnserrSPLIT_1)
{
IDTSInputColumn100 inputColumn = destDesignTimeSPLIT_1.SetUsageType(destinationInputerrSPLIT_1.ID,
destinationVirtualInputerrSPLIT_1, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
IDTSExternalMetadataColumn100 externalColumn = destinationInputerrSPLIT_1.ExternalMetadataColumnCollection[inputColumn.Name];
destDesignTimeSPLIT_1.MapInputColumn(destinationInputerrSPLIT_1.ID, inputColumn.ID, externalColumn.ID);
}
foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumnserrSPLIT_2)
{
IDTSInputColumn100 inputColumn = destDesignTimeSPLIT_2.SetUsageType(destinationInputerrSPLIT_2.ID,
destinationVirtualInputerrSPLIT_2, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
IDTSExternalMetadataColumn100 externalColumn = destinationInputerrSPLIT_2.ExternalMetadataColumnCollection[inputColumn.Name];
destDesignTimeSPLIT_2.MapInputColumn(destinationInputerrSPLIT_2.ID, inputColumn.ID, externalColumn.ID);
}
Rename the two OLEDB destination components:
destinationSPLIT_1.Name = “OLEDBDestination_SPLIT_1”;
destinationSPLIT_2.Name = “OLE DB Destination_SPLIT_2”;
We then save the package into a file system.
Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\\\TEMP\\\\pkg_Execute_Sql_Tasks_Conditional_Split.dtsx\”, dyna_pkg, null);
Conclusion
It’s that simple!
You can now execute your script task and the package will be created in the location you specified.
Wow, Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
thanks
Hello There. I found your blog using msn. This is a very well written article. I’ll be sure to bookmark it and return to read more of your useful info. Thanks for the post. I’ll certainly return.
I appreciate it
Hi there, You’ve done a fantastic job. I’ll definitely digg it and individually recommend to my friends. I am confident they’ll be benefited from this web site.
I appreciate it, thanks
“ Im curious if you ever have problems with what people post? Honestly the internet used to be like a different place, although it seems to be changing for the better. What do you think? “
couldnt agree more
Appreciate it for all your efforts that you have put in this. very interesting information.
my pleasure
In my implementation this sample code errors out at:
data_Split_Wrapper.SetOutputProperty(splitOutput_1.ID, “FriendlyExpression”, “[Gender] == \”M\””);
Please help!
I tried other simpler expressions but still get the following error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Runtime.InteropServices.COMException (0xC0204006): Exception from HRESULT: 0xC0204006
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetOutputProperty(Int32 lOutputID, String PropertyName, Object vValue)
at ST_dc72e2704d094f3cba3ff16f1aa95541.csproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Hi there,
Let me have a look and will get back to you shortly
Regards,
Sifiso
Thanks for quick follow up.
If possible, please share the dtsx in email.
Looking forward to your response.
Hi Mo,
I’ve had a look and made changes to the package.
You can download the package here – http://www.blogs.selectsifiso.com/wp-content/uploads/sS_ConditionalSplit.zip
The definition for output tables can be found here http://www.blogs.selectsifiso.com/wp-content/uploads/output_table_definitions.zip
Let me know if you have any issues.
PS: The assemblies used in the Script Task point to Visual Studio/SQL Server 2008, so make sure that you run the script within such an environment.
Regards,
Sifiso
Sifiso:
Thanks a bunch, it worked!
I sincerely appreciate your help.
You have a exciting skill set. Hope to be connected with you professionally, I will send you a linked in invite.
Regards.
my pleasure