none
Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

    Question

  • Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

    Thanks!

     

    Sunday, March 26, 2006 12:44 AM

Answers

  • Hi everyone,

     

    I've been working on this issue since one week.

     

    Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

    It is not that complicated except that the api is quite undocumented...

     

    My program is doing the following task :

     

    #1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

    #2 - Using Package API, I create a package from a template

    #3 - With the load order, I create a DataFlow Task using API that contain :

      A) Source

      B) LookUp

      C) Destination

     

    We will save some precious time on refactoring aspect using this class...

     

    Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

     

    Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

     

    I hope It can help you

     

    #region Map MetaData
    public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90
    dstComp)
    {

    IDTSInput90
    destinationInput = dstMetaData.InputCollection[0];
    int destinationInputID = destinationInput.ID;
    IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
    foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
    {

    // This will create an input column on the component.
    dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType
    .UT_READONLY);
    // Get input column.
    IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
    // Getting the corresponding external column.
    // Ex : We will use the column name as the basis for matching data flow columns to external columns.
    IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
    // Tell the component how to map.
    dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
    }
    }
    #endregion

     


    Wednesday, March 28, 2007 3:45 AM

All replies

  • Done a bit, loading and poking around and some creating directly in code.
    Monday, March 27, 2006 8:58 AM
    Moderator
  • Hi Darren ...

    I've taken a look around SQLIS.  Very helpful indeed!

    Do you have any examples of something as simple as the following, using C# and the object model:

    Source:  SQL Server A, Table Foo

    Transformations:  None

    Target SQL Server B, Table FooTwo

     

    The real trick with the above is that at runtime I won't know what the tables are.  I need to work w/ a SQL String and the re-initializemetadata stuff.

    Looking for any help here.

    Thanks!

    Monday, March 27, 2006 3:16 PM
  • I would probably rebuild the package each time, and Books Online covers this quite well I think.

    Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

    The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

    Monday, March 27, 2006 3:21 PM
    Moderator
  • Thank you very much for the suggestions! 

    I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc.  My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture."  I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

    Any other suggestions?

    All the best,

    DB

    Monday, March 27, 2006 7:08 PM
  • Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

    Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

    -Jamie

     

    Monday, March 27, 2006 7:28 PM
    Moderator
  • Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

    I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

    Tuesday, March 28, 2006 7:28 AM
    Moderator
  • Thanks to both of you for the supportive words.  I'm doing my best to solve this problem.  I'm sure once I get a few components to work it becomes more of a cookie cutter process. 

    Here's an example of a problem I'm trying to solve using the Object Model.  If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help!  It *seems* that this should be so simple!

    In a nutshell:

    I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server.  With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

    The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages. 

    In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300.  Earlier posts in this forum said to accomplish this requires C# and the object model.  Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

    In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly. 

    So far I'm having success with:

    -  Creating the initial package

    -  Creating the MainPipe

    -  Creating source and target OLE-DB Connection Managers

    -  Setting up a source component (see issues below)

    -  Doing an Instantiate and ProvideComponentProperties

    -  Associating the source component with the source connection manager

    -  Using SetComponentProperties to set the SQL statement (see issues below)

    -  Connecting to the source and running reinitializemetadata

     

    -  Repeating the above for the destination component

     

    -  Setting up a path between the two

     

    Here are the issues that I'm pretty sure are screwing me up.  I'm having a hard time with:

    1.  With the source component, all I want is "select col1, col2 from foo".  I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output.  I am running the reinitializemetadata step, but I don't *think* this is enough by itself.  I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step.  I haven't been able to follow BOL to put this together.

    2.  I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component.  I looked at

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm 

    as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.)  The idea of having this as a dynamic list would be extremely helpful for my particular problem.

    3.  OK, once my data shows up at my destination component if possible I need to UPSERT the data.  A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

    4.  Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty.  For example:

    mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

    mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

    mySrcDTInstance.SetComponentProperty("AccessMode", 0);

    I've run every search I could think of and have posted the question before:  What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

    Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

    DB

     

    Tuesday, March 28, 2006 4:08 PM
  • Hi Doug B,

                       We are facing the same problem here.  We need to move 200 tables with Data Flow Task.  I was wondering if you figure out a solution since the last posting.

    Mathieu

    Monday, March 19, 2007 4:05 PM
  •  Doug B wrote:

    I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server.  With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

    I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>".  I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

    Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

    I hope the subtle distinction is clear here.

    -Jamie

     

    Monday, March 19, 2007 10:27 PM
    Moderator
  •  Doug B wrote:

    3.  OK, once my data shows up at my destination component if possible I need to UPSERT the data.  A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty

    That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

    Checking if a row exists and if it does, has it changed?
    http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

     

    -Jamie

     

    Monday, March 19, 2007 10:32 PM
    Moderator
  •  Doug B wrote:

    1.  With the source component, all I want is "select col1, col2 from foo".  I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output.  I am running the reinitializemetadata step, but I don't *think* this is enough by itself.  I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step.  I haven't been able to follow BOL to put this together.

    I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

    -Jamie

     

    Monday, March 19, 2007 10:35 PM
    Moderator
  • Hi Mathieu ...

    Unfortunately we were not able to get this to work.  Perhaps someone with more C# experience would have had better luck.

    All the best,

    Doug

    Tuesday, March 20, 2007 2:54 PM
  • Hi everyone,

     

    I've been working on this issue since one week.

     

    Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

    It is not that complicated except that the api is quite undocumented...

     

    My program is doing the following task :

     

    #1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

    #2 - Using Package API, I create a package from a template

    #3 - With the load order, I create a DataFlow Task using API that contain :

      A) Source

      B) LookUp

      C) Destination

     

    We will save some precious time on refactoring aspect using this class...

     

    Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

     

    Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

     

    I hope It can help you

     

    #region Map MetaData
    public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90
    dstComp)
    {

    IDTSInput90
    destinationInput = dstMetaData.InputCollection[0];
    int destinationInputID = destinationInput.ID;
    IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
    foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
    {

    // This will create an input column on the component.
    dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType
    .UT_READONLY);
    // Get input column.
    IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
    // Getting the corresponding external column.
    // Ex : We will use the column name as the basis for matching data flow columns to external columns.
    IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
    // Tell the component how to map.
    dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
    }
    }
    #endregion

     


    Wednesday, March 28, 2007 3:45 AM
  • Hi,

    I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

    got problem in adding lookup transformation.

    How can I get reference table column output.& how can I set join column property for specific column

     

    plz help.

     

    Wednesday, June 20, 2007 2:45 PM
  • Try this thread for more information on using lookups programatically -  http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1766590&SiteID=1
    Tuesday, June 26, 2007 8:26 AM
    Moderator
  • Howzit Mathieu_N

     

    Is there any possibility that you can share the code that you use to create 200+ dataflows containing the lookup transformation components. (as said in your post)

     

    I've been struggling for the past 2 weeks to get the column mapping and lookup mapping right when using a lookup transformation. (And this is not very well documented anywhere, I can't find decent help anywhere!) To get my required results I also need to set the DTSRowDisposion to "RD_RedirectRow", this however causes the lookup component to be corrupt or something. (really frustrating....)

     

    I am creating a package programmatically using C#.

    The dataflow should contain two lookups (one to retrieve "new and changed" results, the second to split the results into "only new" and "only changed".

     

    I have found when creating the dataflow within the SSIS GUI, this works perfectly, but I just can't seem to get the column mapping right when creating it through C#.

    I have to do the package creation programmatically as I will use this solution to build various comparison solutions in the future.

     

    Any help will be appreciated!!!

     

    Thanks

    Tuesday, April 01, 2008 12:42 PM
  • Hi you will get a detail informaition about programmin SSIS using C# or VB .net in the below link...

    http://www.sqllion.com
    Sunday, May 17, 2009 2:30 PM