Create Script Component as Source Programmatically

Question

I am working on programmatically creating a package with a data flow task containing a Script Component as a Source. I have been able to create the package, data flow task, and add a Script Component. However, the Script Component appears to default to a Transform.

Does anyone know how to get it to be a Souce?

Here is my class with the single method I'm working on:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DynamicPackageCreator.Models;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
// Alias to prevent ambiguity
using dtsColumnDataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType;

namespace DynamicPackageCreator
{
    public class DtsClient
    {
        public void CreatePackageWithDataFlowAndScriptSource(string filePath, string dataFlowName, string sourceName, List<OutputDefinition> outputDefinitions)
        {
            // Create the Package
            Package pkg = new Package();
            pkg.Name = Path.GetFileNameWithoutExtension(filePath);

            // Create the Dataflow task
            Executable e = pkg.Executables.Add("STOCK:PipelineTask");
            TaskHost thMainPipe = e as TaskHost;
            thMainPipe.Name = dataFlowName;
            MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

            // Create Source Component
            IDTSComponentMetaData100 sourceComponent = dataFlowTask.ComponentMetaDataCollection.New();
            sourceComponent.Name = sourceName;
            sourceComponent.ComponentClassID = SsisComponentType.ScriptComponent.GetComponentClassId();

            // Get the design time srcDesignTime of the component
            CManagedComponentWrapper srcDesignTime = sourceComponent.Instantiate();

            // Initialize the component
            srcDesignTime.ProvideComponentProperties();

            int lastOutputId = 0;
            // Add metadata
            foreach (var outputDefinition in outputDefinitions)
            {
                var output = srcDesignTime.InsertOutput(DTSInsertPlacement.IP_AFTER, lastOutputId);
                output.Name = outputDefinition.OutputName;
                lastOutputId = output.ID;

                var outputColumnCollection = output.OutputColumnCollection;
                foreach (var outputColumnDefinition in outputDefinition.OutputColumnDefinitions)
                {
                    var outputColumn = outputColumnCollection.New();
                    outputColumn.Name = outputColumnDefinition.ColumnName;
                    outputColumn.SetDataTypeProperties(dtsColumnDataType.DT_WSTR, outputColumnDefinition.ColumnSize, 0, 0, 0);
                }
            }

            // Reinitialise the metadata
            srcDesignTime.ReinitializeMetaData();

            // Save the package
            Application app = new Application();
            app.SaveToXml(filePath, pkg, null);
        }
    }
}

The OutputDefinition class is a custom class I created for holding the definitions used when creating the outputs.

Josh Jay · Accepted Answer · 2014-10-13 14:31:58Z

1

So, the solution to this issue is to remove all inputs from the component. By default the component has an "Input 0" and an "Output 0" which correlates to being a Transform script component type. A source type would have no Inputs, and a destination would have no Outputs.

To remove the inputs and outputs, add:

sourceComponent.OutputCollection.RemoveAll();
sourceComponent.InputCollection.RemoveAll();

Here:

// ...
// Initialize the component
srcDesignTime.ProvideComponentProperties();

// Remove default inputs and outputs
sourceComponent.OutputCollection.RemoveAll();
sourceComponent.InputCollection.RemoveAll();

int lastOutputId = 0;
// ...

answered Oct 13, 2014 at 14:31

Josh Jay

1,2502 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Script Component as Source Programmatically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related