locked
Dynamic OLE DB Source and Destination RRS feed

  • Question

  • Hi,

    I am building SSIS for 3 different files that have identical
    schema and mapping logic.

    In my OLE DB Source (object name - "OLEDBSource_SourceTable")
    Data Access mode is "Variable name".
    As soon as I swithced to this Data Acces mode
    it started to give me an error:

    [OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
    with the data source columns.

    The column "DEAL_NUM" needs to be updated in the external metadata column collection.
    The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
    from the external metadata column collection.
    The "external metadata column "recordID" (33740)"
    needs to be removed from the external metadata column collection.
    Tuesday, March 27, 2007 9:12 PM

Answers

  • Everything turned out to be much easier, gentlemen!
    As soon as I set in my OLE DB Source Properties
    "ValidateExternalData" to False
    it starts working.
    I guess when your source is a variable it should be this way?

    Robert
    Wednesday, March 28, 2007 1:27 PM
  • Here are the options I am aware of for this:

     

    1. Create SSIS packages to move and delete the data. This would require one data flow per table.
    2. Programmatically create SSIS packages. I provided a link above with a sample.
    3. Use transaction replication, and apply a where clause to what is replicated. This still involves some complexity, as you would have to replicate the archive data, but prevent the deletion of that data from being replicated to the archive database.
    4. Use dynamically generated INSERT INTO..SELECT statements to move the data. Would possibly require a linked server, or at least cross-database SQL. This is probably the simplest option, but you've already said you don't want to do this.
    5. Use the Bulk Insert Task in a ForEach loop. This would handle generically loading the archive database, bu you'd still have to define a generic way to get tab delimited files exported for each of your tables (you might look at the bcp utility).
    Tuesday, March 4, 2008 3:47 PM

All replies

  • Meta data CANNOT change.  You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table.  How can you imagine that working?

    Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic. 

    Bottom line, SSIS relies on meta data.  If you change it, how do you expect it to operate?
    Tuesday, March 27, 2007 9:17 PM
  • Hi Riga,

     

       To make the data source dynamic you need to make the ConnectionString dynamic.

     

    Hope this helps,

    Andy

    Tuesday, March 27, 2007 10:36 PM
  • Everything turned out to be much easier, gentlemen!
    As soon as I set in my OLE DB Source Properties
    "ValidateExternalData" to False
    it starts working.
    I guess when your source is a variable it should be this way?

    Robert
    Wednesday, March 28, 2007 1:27 PM
  • Robert,

     

    Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

     

    Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

    Wednesday, March 28, 2007 2:12 PM
  • I have a variable [SourceTable] that keeps a table name.
    I am using it in my OLE DB Source Data Access Mode.
    But with ValidateExternalMetadata=True it didn't work.
    so I changed it to ValidateExternalMetadata=False
    and it works now.

    I don't know why Phil says it can not be done.
    Even if I hold a table name in a variable
    the bottom line is that all the tables have identical schema.

    So as long as you set a default value of {SourceTable}
    let's say to "Table1" it will work when
    {SourceTable} changes to "Table2","Table3" and so on.

    Robert
    Wednesday, March 28, 2007 2:20 PM
  • I have almost the same requirements as this. The OLEDB source and destination are dynamic.

    However, the catch here is it loops for different tables from a result set. So METADATA should be dynamic also.

     

    From what I've read here, metadata cannot be change during run time. Its a one time set up. But is there other way to  copy data from OLTP and move it to an Archive DB. As I've said, the tables are dynamic, including the conditioning statement (column name and column value). I have no problem of getting the dynamic stuff in here. I just wanna move the result set from OLTP to the Archice DB using the data flow task. Or if there are other ways to do it Smile)

     

     

     

    Saturday, March 1, 2008 12:49 AM
  • You are correct, you can't change the metadata at runtime.You could create the packages programatically (http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

    ).

     

    However, I would recommend that you look at replication or mirroring if you just want a copy of the database.

    Sunday, March 2, 2008 3:58 AM
  • I dont think mirroring can do my specific requirement. It's an archival plan. The idea is to move certain data from OLTP to the Archive DB. All variables are dynamic -- table, clulumn name and column value.

    If I have a table that define what to move with the following values:
    Table Name         PrimaryKeyColumn      KeyValue     
    Movie                          ID                        1000
    Actor                          TransID                 5000

    I would want to move the data of Movie where ID <= 1000, of Actor where TransID <= 5000 from the OLTP to the ArchiveDB. Then delete from OLTP with the same condition.

    I tried using SSIS and loop thru the table. The OLE DB Source and Destination are dynamic, I used variables. However, the catch here is the METADATA component when designing the data flow task.  If my source table has 10 columns (mapped during the desingning, using a default value of those variables -- table name, column name and column value), when the loop run, the second table has 20 columns on it. So it results to an error.

     

    So I'm thingking of other ways to do this. Do you think replication can do this? 

    Monday, March 3, 2008 6:00 PM
  • I'm not an expert on replication, but I don't think it would handle it directly. Replication only copies data, it won't delete the historical rows.

     

    I'd look at the programmatic option I linked to above.

    Monday, March 3, 2008 6:19 PM
  • I can create a separate procedure to delete historical data base on the same condition. Deletion can be done separately. I just wanna get all data with specific condition and transfer it to an Archive DB. We dont want to use linked server too (INSERT INTO ArchiveTable SELECT {Columns here} FROM ServerName.Databasename.dbo.OLTPTable WHERE {condition}).

     

    Unfortunately, Im a DBA. I am not really into programming Sad

     

    Sure, ill look into replication.

     

    Monday, March 3, 2008 6:29 PM
  • I don't know if I understand your requirements 100%, but this might be something to look at:

     

    1) In the source database, create a new staging table to store data to be moved

    2) Create a DELETE trigger on the source table, that inserts the deleted records into the new staging table

    3) Build some sort of batch process (probably a SQL script executed by a SQL Server Agent job, but it could be anything)  that deletes records from the source table, thus causing the trigger to fire

    4) Use this staging table as your data source for the SSIS packages

     

    If this approach will not work, just let us know why and perhaps we can refine it a bit.

     

    Monday, March 3, 2008 7:20 PM
  • So I will have to create one package per table?

     

    The OLTP DB and Archive DB are of two separate servers. We have like hundreds of tables. Deletion is not a problem since I can issue a delete command (base on a condition), and this can be done on Execute SQL task job. I can loop through the table list for deletion and issue a execute sql job -- this is feasible.

     

    However, the archiving thing is somewhat hard to implement. For what I understood from your suggestion, I still have to create one data flow per table coz for each table it has different metadata column mappings. I cannot use OLEDB Source and destination and just loop through the table list for archive.

     

    ->We wanted to use SSIS package to transfer data from source to destination -- this is the first requirement

    ->The SSIS package should be capable of iterating through a table list to archive -- second requirement

    ->SSIS deletes records from OLTP DB to Archive DB once the archiving process id done. Or this can be a separate pacakge. -- 3rd requirement which is already done.

     

    The table list Im talking about is a user table (which is administer by an application -- this is done also), with definition as follows:

    Table Name         PrimaryKeyColumn      Keyvalue    
    Table1                          ID                         1000
    Table2                          TransID                 5000
    Table3                          LogID                    7000

     

    Base from that list (which can be more than 3 tables), we would want to move the data of Table 1 where ID <= 1000, of Table2 where TransID <= 5000, of Table3 where LogID <= 7000 from the OLTP to the ArchiveDB. Then delete from OLTP with the same condition (3rd requirement which is done already)

    Using OLEDB Source and destination data flow tasks is not feasible to iterate through a list of table. A column mapping is needed during design mode, which during run time mode, the table columns are dynamic.

     

    It is not also feasible for us to create one package per table. So im looking of other ways to do this. Is there any other job task on SSIS to handle this style of scenario?

     

    Monday, March 3, 2008 8:13 PM
  •  

    If my first source table has 10 columns (mapped during the desingning, using a default value of those variables -- table name, column name and column value) -- it went okay. When the loop goes to second record, the second table has 20 columns on it. It gets an error something to do with metadata thing.
    Monday, March 3, 2008 8:23 PM
  • Here are the options I am aware of for this:

     

    1. Create SSIS packages to move and delete the data. This would require one data flow per table.
    2. Programmatically create SSIS packages. I provided a link above with a sample.
    3. Use transaction replication, and apply a where clause to what is replicated. This still involves some complexity, as you would have to replicate the archive data, but prevent the deletion of that data from being replicated to the archive database.
    4. Use dynamically generated INSERT INTO..SELECT statements to move the data. Would possibly require a linked server, or at least cross-database SQL. This is probably the simplest option, but you've already said you don't want to do this.
    5. Use the Bulk Insert Task in a ForEach loop. This would handle generically loading the archive database, bu you'd still have to define a generic way to get tab delimited files exported for each of your tables (you might look at the bcp utility).
    Tuesday, March 4, 2008 3:47 PM
  • Thanks for the suggestions jwelch!

     

    I might go for option 1 Smile

    Thursday, March 6, 2008 10:24 PM
  • I had a situation where I had to transfer  n number of tables where n was dynamic (n>1500)
    I compared performance between transferring data using SSIS objects in C# and using SqlBulkCopy class, and SqlBulkCopy was much better, because I didnt have any transformation between my souce and destination.

    I saw more memory pressure, more CPU usage when using SSIS objects, also it took more time to execute.

    Thanks, Atul
    Tuesday, July 21, 2009 7:25 AM
  • I had a situation where I had to transfer  n number of tables where n was dynamic (n>1500)
    I compared performance between transferring data using SSIS objects in C# and using SqlBulkCopy class, and SqlBulkCopy was much better, because I didnt have any transformation between my souce and destination.

    I saw more memory pressure, more CPU usage when using SSIS objects, also it took more time to execute.

    Thanks, Atul
    Thanks Atul, this one helped me very much.
    GB
    Tuesday, July 21, 2009 5:34 PM
  • Hi,

    I need to create SSIS packages to load data from 3 different databases (each with same table structure) into a single database with the tables having same structure however each on a different schema. For example

    Source Database              Tables
    Database A                      TableA , TableB
    Database B                      TableA , TableB

    Destination Database          Tables
    DestDatabase                    SchemaA.TableA, SchemaB.TableA,
                                            SchemaA.TableB, SchemaB.TableB

    I need to load
     a. TableA from Database A to SchemaA.TableA
     a. TableA from Database B to SchemaB.TableA
     a. TableB from Database A to SchemaA.TableB
     a. TableB from Database B to SchemaB.TableB

    The number of tables is around 150 in each of the source databases.  Most of the tables will have their primary key set as Identity.

    Please provide me with technical suggestions/examples.

    Thanks
    Sugavaneswaran

    Friday, September 11, 2009 12:28 PM
  • Meta data CANNOT change.  You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table.  How can you imagine that working?

    Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic. 

    Bottom line, SSIS relies on meta data.  If you change it, how do you expect it to operate?

    This line of thinking is not entirely correct. The whole purpose of BIDS, the very design of the program, is premised around the dynamic movement of data.

    As a simple solution to the original question, "Expressions" can be used to dynamically alter the InitialCatalog property of a Connection Manager. This will change which database the data is sent to.

    Thursday, July 1, 2010 8:37 PM
  • Dear riga66:

    You sir,or madam, are a lifesaver!  

    Even though you probably don't work in the BI space anymore, and probably won't see this... Here it is almost 9 years later, and your post solved a problem that my colleague and I have been struggling with for a couple of days: sharepoint lists in a for-loop.

    Thank you riga66.  From the bottom of my heart; thank you.


    Cheers and thanks, Simon

    Thursday, January 21, 2016 10:51 PM