locked
Problem with dynamic OLEDB Source and Destination RRS feed

  • Question


  • Hi Everyone,

      Im working in a DTS to backup tables from a different server, i understand the only way is data flow task, oledb source/destination. there are like 500 tables, So, i have a table with selects that im using like parameters, and assigning that parameters to 3 variables in DTS.
    Select, to save the dataset. Delete, to delete the information previously copied. and Table, to assign the name of OLEDB Destination table.
    To understand more easy the problem is, i have my Dataflow with Dynamic values passed by variables.
    I was reading a lot how to do that, because Dataflow cannot be modified in runtime. Im not sure if i understand well or there are some other method to do that.
    So i generated a Package Parent to read the variables and a for each, it read the values of the parameter table and assign values to an Execute Package. It call to children Package, who execute Dataflow task. Im receiving the variables but i cant use it in the Dataflow because my OleDBSource and Destination seems like empty.
    Is it possible or how i can do that?
    This is the error message: Data Flow Task, DTS.Pipeline: "output "OLE DB Source Output" (11)" contains no output columns.

    Wednesday, April 11, 2012 1:27 PM

Answers

  • You can not use SSIS to transfer records with dynamic metadata from a source to a destination.  SSIS requires metadata be set at design-time, and remain static through to runtime.

    If you wish to use SSIS in a dynamic fashion, you may have to programatically generate packages using the SSIS API.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by Koen VerbeeckMVP Thursday, April 12, 2012 7:41 AM
    • Marked as answer by Betzero Tuesday, April 24, 2012 1:58 PM
    Wednesday, April 11, 2012 4:12 PM
  • To programatically create packages, you do not use BIDS at all.  You need to use Visual Studio with C# or VB.Net to use the SSIS API to construct packages using API calls.

    There is a framework that attempts to simplify some of this on CodePlex: EzAPI.


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by Eileen Zhao Wednesday, April 18, 2012 7:12 AM
    Wednesday, April 11, 2012 10:06 PM
  • Like Todd said SSIS requires metadata to work with and if you want to have Dynamic metadata you need to custom build it.

    I was task last time to transform a complex xml into relational database model with having over 300 tables, i can just build 300 dataflows

    but the requirement states that I should support multiple versions of xml data, do a complex validation, and tables can be shared on different versions.

    I end up building my own ETL solution using C#.

    http://randypaulo.wordpress.com/2011/12/15/pure-c-etl-extract-transform-load/

    It requires a combination of IoC containers, I used Unity 2.0 during that time and you can't use .NET MEF since SSIS supports only up to .NET 3.5 and AutoMapper a library that automap objects based on the name of columns (auto mapping). And LINQ/Entity Framework to insert data (Automapper maps from Deserialized data to Data Entities), it's fast for inserting records < 1000 but slow when it's more than that. For Bigger row count I passed the deserialized data directly to a stored procedure that does the bulk insert.


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog


    BizTalk Message Archiving - SQL and File
    Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
    Sending IDOCs using SSIS

    • Marked as answer by Eileen Zhao Wednesday, April 18, 2012 7:13 AM
    Thursday, April 12, 2012 4:34 AM

All replies

  • You can not use SSIS to transfer records with dynamic metadata from a source to a destination.  SSIS requires metadata be set at design-time, and remain static through to runtime.

    If you wish to use SSIS in a dynamic fashion, you may have to programatically generate packages using the SSIS API.


    Todd McDermid's Blog Talk to me now on

    • Proposed as answer by Koen VerbeeckMVP Thursday, April 12, 2012 7:41 AM
    • Marked as answer by Betzero Tuesday, April 24, 2012 1:58 PM
    Wednesday, April 11, 2012 4:12 PM
  • Hi Todd, i supossed it but want to be sure, appreciate a lot your answer. So, now im reading a lot about programatically packages. But i have some doubts. Can you tell me how it works? I mean, im not sure if it is an SCRIPT TASK in the package or some kind of project different.

    And which part of the code need to use for my model, i mean, i need to do only the TASK FLOW CONTROL? or all the package? I understand i need to reinitialize every time that the pack run with the columns of my table. It is ok?

    Thanks a lot. Alberto.

    Wednesday, April 11, 2012 5:22 PM
  • To programatically create packages, you do not use BIDS at all.  You need to use Visual Studio with C# or VB.Net to use the SSIS API to construct packages using API calls.

    There is a framework that attempts to simplify some of this on CodePlex: EzAPI.


    Todd McDermid's Blog Talk to me now on

    • Marked as answer by Eileen Zhao Wednesday, April 18, 2012 7:12 AM
    Wednesday, April 11, 2012 10:06 PM
  • Hi Todd,

    it's not a relevent question for this thread but i am facing same sort of issue and will resolve through SSIS API. but i am wondering is there any ETL Tool which support Dynamic meta data behaviour at run time.

    Thanks,

    Zaim Raza.

    Thursday, April 12, 2012 2:13 AM
  • Like Todd said SSIS requires metadata to work with and if you want to have Dynamic metadata you need to custom build it.

    I was task last time to transform a complex xml into relational database model with having over 300 tables, i can just build 300 dataflows

    but the requirement states that I should support multiple versions of xml data, do a complex validation, and tables can be shared on different versions.

    I end up building my own ETL solution using C#.

    http://randypaulo.wordpress.com/2011/12/15/pure-c-etl-extract-transform-load/

    It requires a combination of IoC containers, I used Unity 2.0 during that time and you can't use .NET MEF since SSIS supports only up to .NET 3.5 and AutoMapper a library that automap objects based on the name of columns (auto mapping). And LINQ/Entity Framework to insert data (Automapper maps from Deserialized data to Data Entities), it's fast for inserting records < 1000 but slow when it's more than that. For Bigger row count I passed the deserialized data directly to a stored procedure that does the bulk insert.


    Randy Aldrich Paulo

    MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog


    BizTalk Message Archiving - SQL and File
    Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
    Sending IDOCs using SSIS

    • Marked as answer by Eileen Zhao Wednesday, April 18, 2012 7:13 AM
    Thursday, April 12, 2012 4:34 AM
  • ... but i am wondering is there any ETL Tool which support Dynamic meta data behaviour at run time.

    Yes. CozyRoc has a commercial component for SSIS - dataflowtask+ - that allows dynamic metadata.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    Thursday, April 12, 2012 7:42 AM