Post reply

How to configure a data flow at runtime

  • July 4, 2007 at 12:36 am

    #119671

    Hi,

    I'm trying to build a SSIS package to transfer data and data structures from a SQL Server database to an Access MDB. Some column data types have to be modified, some columns dare not to be transferred. The information, which columns to transfer, are located in a table "ExportConfiguration".

    What I did so far: I set up a package flow that has as the "core" component a for-each-container that iterates through all the tables that are to be processed. Each iteration creates one table structure and one select-statement, that should be used to read the data from the sql server.

    And here is my problem: As I don't know the metadata of the data flow at design time, how can I transfer the data? Some thoughts:

    1. Build a dummy data flow (only source-path-destination) at design time and before that a script task to modify the select statement of the source and the insert statement of the destination. But how can I refer to my own package in the script and what do I have to modify?
    2. Doing it all in a script task, i. e. create an access instance, create a link to the SQL Server table in the access database and executing an insert into statement on the access connection.
    3. Prepare everything that has to be transferred in SQL Server, i. e. a temporary database. Then copy the tables 1:1 to the MDB as the export/import-wizard does. But how can I call this functionality from my package?

    What would you do and how?

    Thank you in advance

    Alexander

  • July 4, 2007 at 1:41 am

    #717071

    You cannot change the meta data at run time for a data flow.

    Your best bet is to dynamically create SQL to do the select and insert and execute it as a SQL statement.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply