CODEWITHSUNDEEP
c#ssis
Tempest
Tempest

Reputation: 25

Creating a RecordsetDestination programmatically

I am trying to build an SSIS package with C#.

The problem I am having is that I cant work out how to create a RecordsetDestination and bind it to an OLEDB data source that returns the results from a SqlCommand

I can find loads of examples binding to an OLE destination, but I cant work out how to bind to a Recordset.

so far I have been able to create my dataflow task create the Ole Source, add an Object Variable to store the recordset results in. But... I don't know how to create the recordset destination itself and then bind it to the source and to the varable.

Upvotes: 1

Views: 104

Answers (1)

Sam Kolli
Sam Kolli

Reputation: 421

You will have a very similar process to how you are creating your Oledb source to create the Record Set Destination component; with some small changes.

  1. Set the ComponentClassID of the corresponding IDTSComponentMetaData100 object to either "{C457FD7E-CE98-4C4B-AEFE-F3AE0044F181}" or "DtsAdapter.RecordSetDestination". Assuming recSetComponentMetadataObject is that object, it will look like this:

    recSetComponentMetadataObject.ComponentClassID = "{C457FD7E-CE98-4C4B-AEFE-F3AE0044F181}";

  2. Set the "VariableName" property on the RecordSet destination component to your user variable. To do this, you will call the SetComponentProperty on the corresponding CManagedComponentWrapper/IDTSDesigntimeComponent100 object. Assuming recSetDesignTimeObject is that object, it will look like this:

    recSetDesignTimeObject.SetComponentProperty("VariableName", "User::MyRecSetVariableName");

  3. Create a new IDTSPath100 connecting the Source component and the Record Set Destination
  4. Set the usage type on the columns you want to use in the Record Set Destination (i.e. the ones that would be stored into the user variable).

You will need the same assembly references that you are using to create the OleDb Source component.

//Record Set Destination Component Instantiation
        IDTSComponentMetaData100 recSetComponentMetadataObject = pipeline.ComponentMetaDataCollection.New();
        recSetComponentMetadataObject.ComponentClassID = "{C457FD7E-CE98-4C4B-AEFE-F3AE0044F181}";
        //recSetComponentMetadataObject.ComponentClassID = "DtsAdapter.RecordSetDestination";
        CManagedComponentWrapper recSetDesignTimeObject = recSetComponentMetadataObject.Instantiate();
        recSetDesignTimeObject.ProvideComponentProperties();

        // name, description etc if needed
        recSetComponentMetadataObject.Name = "HoHoHo";

        // Setting the User Variable
        recSetDesignTimeObject.SetComponentProperty("VariableName", "User::MyRecSetVariableName");

        // Connect to previous component;
        // Assuming pipeline is the MainPipe object from the parent Data Flow Task and srcComponent is the IDTSComponentMetaData100 object of previous oledb source component
        IDTSPath100 path = pipeline.PathCollection.New();
        path.AttachPathAndPropagateNotifications(
            srcComponent.OutputCollection[0],
            recSetComponentMetadataObject.InputCollection[0]);

        // Set usage type to the required columns
        IDTSInput100 recSetInput = recSetComponentMetadataObject.InputCollection[0];
        IDTSVirtualInput100 virtualInput = recSetInput.GetVirtualInput();

        // call SetUsageType for each column you need. Here I am assuming I need columns "col_a" and col_b
        IDTSInputColumn100 inputColA = recSetDesignTimeObject.SetUsageType(
            recSetInput.ID,
            virtualInput,
            virtualInput.VirtualInputColumnCollection["col_a"].LineageID,
            DTSUsageType.UT_READONLY
            );

        IDTSInputColumn100 inputColB = recSetDesignTimeObject.SetUsageType(
            recSetInput.ID,
            virtualInput,
            virtualInput.VirtualInputColumnCollection["col_b"].LineageID,
            DTSUsageType.UT_READONLY
            );

Upvotes: 1

Related Questions