locked
Metadata errors – OLE DB Source/Destination Components RRS feed

  • Question

  • I’m trying to perform dynamic processing of data to update destination tables with new and changed data. I’m pulling that data from a number of tables in one server to the other (from SQL 2005 to SQL 2008). The good thing is that table names and column names are exactly the same between the source and destination databases. I have already created all the necessary variables and expressions in SSIS to perform these dynamic tasks. My predicament now lies on metadata errors resulting from OLE DB Source and Destination components. May I know if there is a way to make the OLE DB Source and Destination components to dynamically adapt/update metadata definitions in line with the table names/definitions which will be constantly changing in the input variable? I have changed the ValidateExternalMetadata property to False, and I have also changed DelayValidation property to True but still I’m getting metadata errors.

    I hope my question is clear.

    Many thanks,


    Mpumelelo

    Friday, May 24, 2013 11:49 AM

Answers

  • SSIS data flow task does not support dynamic metadata. You have to have different data flow task for the tables.

    Nitesh Rai- Please mark the post as answered if it answers your question

    • Proposed as answer by SSISJoostMVP Friday, May 24, 2013 7:39 PM
    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:04 PM
    Friday, May 24, 2013 12:17 PM
  • I’m trying to perform dynamic processing of data to update destination tables with new and changed data. I’m pulling that data from a number of tables in one server to the other (from SQL 2005 to SQL 2008). The good thing is that table names and column names are exactly the same between the source and destination databases.

    If you are trying to perform upsert (update + insert) on destination tables, I would recommend using T-SQL Merge statement. It is really nice statement that performs this operation in one batch and hence, improved performance over SSIS (tru look ups).


    Thanks, hsbal

    • Proposed as answer by SSISJoostMVP Friday, May 24, 2013 7:39 PM
    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:05 PM
    Friday, May 24, 2013 6:18 PM
  • SSIS does not support dynamic metadata. Alternatives are:

    1) create a data flow for each table manually

    2) create a data flow for each table automatically with f.e. BIML or a .Net API

    3) use tsql statements/queries


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Mike Yin Monday, May 27, 2013 7:21 AM
    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:05 PM
    Friday, May 24, 2013 7:42 PM
  • Thank you everyone for the contributions that you made to address my question. Like I said last week, I managed to get my question addressed. Before I explain how I solved it I just want to respond to some posts with suggested answers which I couldn’t use in my case. One thing which I didn’t mention in my question is that the scenario which I was trying to address is just a temporary measure. The organisation that I work for is moving from SQL 2005 to 2008. 2005 base tables are getting refreshed every night while 2008 tables aren’t but will in the near future. Business has requested that they want up to date data in 2008 ETL run hence I asked that question.

    1. Harry suggested using Merge statement. Unfortunately I opted not to use that approach on purpose even though it was one of the options in my cards before I even posted this question in the forum. The reason for not using Merge statement is that it was going to be too much involving to come up with the appropriate statements for 51 tables that I have to refresh. Besides, some of the tables have columns which are as many as 40 or even more.
    2. When SSISJoost suggestions came through I had already addressed my question.

    Here is what I did. It may not be the most efficient way but it works for me with minimal effort especially given that this is only meant to be temporary.

    1. I created an SSIS package called CreateTempTables.
    2. I then created a static table on the Destination Server Database which I called TableNames. I populated table TableNames with all the names of the tables that I want to refresh.
    3. What I did next was; on CreateTempTables SSIS Control Flow I used an Execute SQL Task to pull all the table names from table TableNames. I then put that list of names in an object variable which I called TableNameList.
    4. The next task was the Foreach Loop which I used to loop through the TableNameList variable.
    5. Variables inside the Foreach Loop task are:
      1. Variable MaxDate of int data type which holds the maximum DataLoadDate from the Destination Server Database tables (one at a time).
      2. Variable called MyTableName to hold the name of a table each time a loop is made.
      3. SELECT statement variable which I called SelectInto of string data type. This variable has “SELECT * INTO Temp_MyTableName  FROM MyTableName WHERE DataLoadDate > MaxDate
    6. What happens here is that each time a table is picked up by the Foreach Loop task; a physical temporary table will be created in the Source Server Database. That temp table will only be populated with the relevant rows which will ultimately be pulled to update the Destination Server Database as explained below.
    7. What I then did once all the temp tables were created was to launch an Export Wizard from the Source Server Database. I then selected all my temp tables and mapped them to the relevant destination tables where the rows get appended. I then asked the wizard to create a package for me with all the relevant tables. I called this package ExportUpdates.
    8. Once the package was created by the Export Wizard I then manually added an Execute SQL Task at the end of the tasks which were automatically created for me by the wizard. This Execute SQL Task has a statement which drops all the temp tables which I created earlier in the Source Server Database.
    9. What I finally did was to put my two packages in a SQL Server Agent job. Package CreateTempTables executes first followed by ExportUpdates package.

    Though this approach may look very long because of the detail that I have put in the explanation, to me it was the fastest in the options that I had.


    Mpumelelo

    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:05 PM
    • Edited by Mpumelelo S Monday, May 27, 2013 12:17 PM
    Monday, May 27, 2013 12:04 PM

All replies

  • SSIS data flow task does not support dynamic metadata. You have to have different data flow task for the tables.

    Nitesh Rai- Please mark the post as answered if it answers your question

    • Proposed as answer by SSISJoostMVP Friday, May 24, 2013 7:39 PM
    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:04 PM
    Friday, May 24, 2013 12:17 PM
  • As Nitesh told, the existing components doesnt support refreshing metadata dynamiccally, but you can create dataflow task dynamically which will perform as you wish.

    Check the below link which will help you in understanding on how to create a dataflow task or package dynamically.

    http://msdn.microsoft.com/en-us/library/ms135946.aspx

    Regards

    Naveen

    Friday, May 24, 2013 12:23 PM
  • Thank you for the responses. Before attempting the programmatic creation of a package as explained in the link provided by Naveen I was just wondering if this other option that I have can work, with your help of course, because I have failed to make it work and I’m not even sure if its supported. The option that I’m talking about is to pull data from the source table and load it into an object variable, then read that data from the object variable and insert it to the destination table. I don’t know how I can read data from the object variable. The setting should be something like below. Please note that @[User::UpDatesLoaded] is the object variable in question.

    "insert into " +  @[User::IPMTableName] +" select * from " +@[User::UpDatesLoaded]

    Which reads:

    insert into IPM_PAT select * from ObjectVariable

    When I try this I get this error

    The data type of variable @[User::UpDatesLoaded] is not supported in an expression.

    Is there any way of reading data from that object variable and then inserting it into the destination table.


    Mpumelelo

    Friday, May 24, 2013 12:39 PM
  • I have come across some information which advises that there is no way of reading data from an object variable without using a script or a ForeachLoop task. Please see this link, http://stackoverflow.com/questions/3395788/pull-data-from-an-object.

    On a different note, I have managed to address my issue. I will post the full details of what I did next week as I am pressed for time at the moment.


    Mpumelelo

    Friday, May 24, 2013 2:04 PM
  • I’m trying to perform dynamic processing of data to update destination tables with new and changed data. I’m pulling that data from a number of tables in one server to the other (from SQL 2005 to SQL 2008). The good thing is that table names and column names are exactly the same between the source and destination databases.

    If you are trying to perform upsert (update + insert) on destination tables, I would recommend using T-SQL Merge statement. It is really nice statement that performs this operation in one batch and hence, improved performance over SSIS (tru look ups).


    Thanks, hsbal

    • Proposed as answer by SSISJoostMVP Friday, May 24, 2013 7:39 PM
    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:05 PM
    Friday, May 24, 2013 6:18 PM
  • SSIS does not support dynamic metadata. Alternatives are:

    1) create a data flow for each table manually

    2) create a data flow for each table automatically with f.e. BIML or a .Net API

    3) use tsql statements/queries


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    • Proposed as answer by Mike Yin Monday, May 27, 2013 7:21 AM
    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:05 PM
    Friday, May 24, 2013 7:42 PM
  • Thank you everyone for the contributions that you made to address my question. Like I said last week, I managed to get my question addressed. Before I explain how I solved it I just want to respond to some posts with suggested answers which I couldn’t use in my case. One thing which I didn’t mention in my question is that the scenario which I was trying to address is just a temporary measure. The organisation that I work for is moving from SQL 2005 to 2008. 2005 base tables are getting refreshed every night while 2008 tables aren’t but will in the near future. Business has requested that they want up to date data in 2008 ETL run hence I asked that question.

    1. Harry suggested using Merge statement. Unfortunately I opted not to use that approach on purpose even though it was one of the options in my cards before I even posted this question in the forum. The reason for not using Merge statement is that it was going to be too much involving to come up with the appropriate statements for 51 tables that I have to refresh. Besides, some of the tables have columns which are as many as 40 or even more.
    2. When SSISJoost suggestions came through I had already addressed my question.

    Here is what I did. It may not be the most efficient way but it works for me with minimal effort especially given that this is only meant to be temporary.

    1. I created an SSIS package called CreateTempTables.
    2. I then created a static table on the Destination Server Database which I called TableNames. I populated table TableNames with all the names of the tables that I want to refresh.
    3. What I did next was; on CreateTempTables SSIS Control Flow I used an Execute SQL Task to pull all the table names from table TableNames. I then put that list of names in an object variable which I called TableNameList.
    4. The next task was the Foreach Loop which I used to loop through the TableNameList variable.
    5. Variables inside the Foreach Loop task are:
      1. Variable MaxDate of int data type which holds the maximum DataLoadDate from the Destination Server Database tables (one at a time).
      2. Variable called MyTableName to hold the name of a table each time a loop is made.
      3. SELECT statement variable which I called SelectInto of string data type. This variable has “SELECT * INTO Temp_MyTableName  FROM MyTableName WHERE DataLoadDate > MaxDate
    6. What happens here is that each time a table is picked up by the Foreach Loop task; a physical temporary table will be created in the Source Server Database. That temp table will only be populated with the relevant rows which will ultimately be pulled to update the Destination Server Database as explained below.
    7. What I then did once all the temp tables were created was to launch an Export Wizard from the Source Server Database. I then selected all my temp tables and mapped them to the relevant destination tables where the rows get appended. I then asked the wizard to create a package for me with all the relevant tables. I called this package ExportUpdates.
    8. Once the package was created by the Export Wizard I then manually added an Execute SQL Task at the end of the tasks which were automatically created for me by the wizard. This Execute SQL Task has a statement which drops all the temp tables which I created earlier in the Source Server Database.
    9. What I finally did was to put my two packages in a SQL Server Agent job. Package CreateTempTables executes first followed by ExportUpdates package.

    Though this approach may look very long because of the detail that I have put in the explanation, to me it was the fastest in the options that I had.


    Mpumelelo

    • Marked as answer by Mpumelelo S Monday, May 27, 2013 12:05 PM
    • Edited by Mpumelelo S Monday, May 27, 2013 12:17 PM
    Monday, May 27, 2013 12:04 PM