Clone a SSIS Data flow via C#

Question

I've created a Package with a SSIS Data flow (Designer mode) named 'User', witch have a ADO.NET source from table 'User' to a ADO.NET Destination table 'UserDest' (columns: id, name). Table columns are identically. Then I have to re-create this same structure of Dataflow to other 100 remaining tables (Creating precedence constraints aditionally, because the import must be done one table per time). My idea is to edit the Package via C#, Clone the structure of the 'User' Data flow, adjust the columns mapping and the ADO.NET table source and destination.

I've started creating a Package variable to start cloning the properties, but the InnerObject returns null:

using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace ConsoleApplication1
{
    public class Gerador
    {
        public string RunPackage2()
        {
            var pkgLocation = @"C:\Users\me\Documents\Visual Studio 2015\Projects\SSISExample1\SSISExample1\Package.dtsx";

            Application app = new Application();
            Package pkg = app.LoadPackage(pkgLocation, null);
            var importUsersHost = pkg.Executables[0] as TaskHost;

            var pipe = importUsersHost.InnerObject as MainPipe; // Here it returns null

            Console.WriteLine();
            return "OK";
        }
    }
}

In resume, I need to generate 100 other Data flows like the 'Users' Data flow, and add a connector one after other (because each Data flow must run one by one).

Dataflow image

Package image

Answer 1

The best approach for you to go about is to use BIML to accomplish this task. You can start with this series - link

Answer 2

Marcelo,

I would check if the first element in the Executables is actually a data flow task or not. What happens if you try accessing the element with the name? Like Executables["my_data_flow_name"] ?

Alternatively, I would ask you to try a tool that I wrote. It is simpler to use than using the Object Model directly. Look at this example and replicate that.

If you dont wanna use the tool I wrote, you can look at the underlying source code in there and directly apply that to the SSIS Object Model. But you might find it easier to use the tool I wrote :-)

UPDATE: I edited my answer to show looping directly in the example and also how the precedence constraints can be added too.