Programmatically Create Data Flow Task in SSIS Package Using C#

Abstract

This article explains how to create an SSIS package with a Data Flow Task using C# programming language.

Requirements

Article

If the above requirements are all met, we will begin by launching Microsoft Visual Studio 2008.

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

After declarations, create an instance of application and package:

Application SIFISO_app = new Application();
Package p = new Package();

Insert a data flow task:

Executable e = p.Executables.Add(“STOCK:PipelineTask”);
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
thMainPipe.Name = “selectSIFISO data Flow”;

Add a source OLEDB connection manager to the package:

ConnectionManager cm = p.Connections.Add(“OLEDB”);
cm.Name = “OLEDB ConnectionManager”;
cm.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_Source_SQL_Server”, “your_Source_SQL_Database”);

Add a destination OLEDB connection manager to the package:

ConnectionManager cm = p.Connections.Add(“OLEDB”);
cm.Name = “OLEDB ConnectionManager”;
cm.ConnectionString = string.Format(
“Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;”, “your_Destination_SQL_Server”, “your_Destination_SQL_Database”);

Add an OLE DB source to the data flow:

IDTSComponentMetaData100 component =
dataFlowTask.ComponentMetaDataCollection.New();
component.Name = “OLEDBSource”;
component.ComponentClassID = “DTSAdapter.OleDbSource.2″;

Get the design time instance of the component and initialize the component:

CManagedComponentWrapper instance = component.Instantiate();

instance.ProvideComponentProperties();

Specify the connection manager:

if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.GetExtendedInterface(p.Connections[0]);
component.RuntimeConnectionCollection[0].ConnectionManagerID =
p.Connections[0].ID; }

Set the custom properties:

instance.SetComponentProperty(“AccessMode”, 2);
instance.SetComponentProperty(“SqlCommand”,
“SELECT * FROM your_source_table”);

Reinitialize the source metadata:

instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

Add the destination component:

IDTSComponentMetaData100 destination =
dataFlowTask.ComponentMetaDataCollection.New();
destination.ComponentClassID = “DTSAdapter.OleDbDestination”;
destination.Name = “OLEDBDestination”;

Get destination design-time instance and initialise component:

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

Set destination connection:

destination.RuntimeConnectionCollection[0].ConnectionManagerID = cm_DES.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(cm_DES);

Set destination table name:

destDesignTime.SetComponentProperty(“OpenRowset”, “your_destination_table”);
destDesignTime.SetComponentProperty(“AccessMode”, 3);
destDesignTime.SetComponentProperty(“FastLoadOptions”, “TABLOCK,CHECK_CONSTRAINTS”);

Connect the source to destination component:

dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(component.OutputCollection[0],
destination.InputCollection[0]);

Get input and virtual input for destination to select and map columns:

IDTSInput100 destinationInputerr = destination.InputCollection[0];
IDTSVirtualInput100 destinationVirtualInputerr = destinationInputerr.GetVirtualInput();
IDTSVirtualInputColumnCollection100 destinationVirtualInputColumnserr =
destinationVirtualInputerr.VirtualInputColumnCollection;

Reinitialize the destination metadata:

destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();

Get the destination’s default input and virtual input:

IDTSInput100 input = destination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();

Iterate through the virtual input column collection:

foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Select column, and retain new input column
IDTSInputColumn100 inputColumn = destDesignTime.SetUsageType(input.ID,
vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
// Find external column by name
IDTSExternalMetadataColumn100 externalColumn =
input.ExternalMetadataColumnCollection[inputColumn.Name];
// Map input column to external column
destDesignTime.MapInputColumn(input.ID, inputColumn.ID, externalColumn.ID);

}
We then save the package into a file system.

Dts.TaskResult = (int)ScriptResults.Success;
SIFISO_app.SaveToXml(“C:\\TEMP\\pkg_create_DTS.dtsx”, dyna_pkg, null);

Conclusion

It’s that simple!

You can now execute your script task and the package will be created in location you specified.

25 Comments

  1. Currently it looks like Drupal is the top blogging platform available right now. (from what I’ve read) Is that what you are using on your blog?

  2. Hello there! Do you know if they make any plugins to protect against hackers? I’m kinda paranoid about losing everything I’ve worked hard on. Any suggestions?

  3. I learned a lot from this article, great help for me, thank you!

  4. My friend will definitely be interested in this. I’ll be sure to share it with him. Thanks

  5. The articles of the blog is very well constructive and very informative, it will help me to improve my skill. Thanks

  6. Zion says:

    Hi,

    Do you have any sample how to add Derived column component and connect it to flat file source and flat file destination?
    Thank you
    Zion

  7. R says:

    hi,

    It seems a great approach to create SSIS packages. I tried to execute this code and work around but i am getting error:

    this error is when you meta data is initialised on follwing code:

    ////Reinitialize the source metadata:
    //instance.AcquireConnections(null);
    //instance.ReinitializeMetaData();
    //instance.ReleaseConnections();

    could you please help me solve this problem?

    Error Message is:

    SSIS package “Package.dtsx” starting.
    Error: 0×1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Runtime.InteropServices.COMException (0xC020801C): Exception from HRESULT: 0xC020801C
    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)
    at ST_79c43139c8af4c06bfddca6fef78f7f4.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()
    Task failed: Script Task
    Warning: 0×80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
    SSIS package “Package.dtsx” finished: Failure.

  8. Akshya says:

    Hi,

    I’m getting package failure when i try to execute.

    Also source Table content is not moved to destination table.
    should we add any additional code for moving the source table content to destination table. pls advice.

  9. Akshya says:

    Getting Error “Error code:0xC0048021component is missing,not registered,not upgradeable,or missing required interfaces”

  10. prash says:

    please tel_me this error i am when i ren the code

    here i am getting this error :

    //Reinitialize the metadata, generating exernal columns from flat file columns
    instanceDestination.AcquireConnections(null);
    instanceDestination.ReinitializeMetaData();
    instanceDestination.ReleaseConnections();
    error message:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Runtime.InteropServices.COMException (0xC020801C): Exception from HRESULT: 0xC020801C
    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction)
    at ST_ed613a29544f459ca3f1e5e8e0a4a016.csproj.ScriptMain.Main() in C:\Users\prashanthk\AppData\Local\Temp\4\SSIS\e334caa9e069460893fd29a731545424\ScriptMain.cs:line 266
    — End of inner exception stack trace —
    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature 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 System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    • Sifiso says:

      Hey Prash,

      That error means the connection string provided is not correct. for example, firstly verify that the database and server address provided has been typed in correctly and that it’s working. Secondly, check the source/target table that you are attempting to reinitialize and ensure that the table exist within the given database/server. if you are referencing a query instead of just passing a table name, ensure that the query can run without errors.

      Regards,

      Sifiso

  11. prash says:

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.ArgumentException: Keyword not supported: ‘provider’.
    at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
    at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
    at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
    at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value)
    at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
    at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
    at ST_3d0872519412449eb1d35fefd547b4fd.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.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature 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 System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

  12. Ravi says:

    Hi Man,

    Thanks Lot,i got Good result because of your Guidelines.

  13. Gaurav Jain says:

    hi sifiso ,
    i want to use XMLSource as source in my project so how should i write code for this .
    Please help me

    • Sifiso says:

      hi,

      apologies for the late response but what version of ssis are you using? with ssis 2012 – there is now the question of project vs. package deployment to consider when extending ssis using scripts.

      regards,

      sifiso

Leave a comment

%d bloggers like this: