Search code examples
sqlssisdynamic-sqlscript-task

How to create a database, table and Insert data into it and use it as a source in another data flow in SSIS?

I have a need to create a SQL database and a table and Insert data into the table from another SQL database . And also to use this newly created database as a oledb source in another dataflow in the same SSIS package. The table and database name are fixed.

I tried using script task to create database and tables. But when I have to insert data , I am not able to give database name in the connection manager as the database is created only in runtime.

I have tried setting ValidExternalMetaData to false, but that doesnt seems to help as well.

Any idea or suggestions on how to accomplish this will be of great help. Thanks

Solution

  • I think you just need two things to make this work:

    1. While developing the package, the database and table will need to exist.
    2. Set DelayValidation to true on the connection manager and dataflow tasks in order to avoid failures with connection tests before they are created.