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

CodePudding user response：

use a variable to hold the new table name create and populate the using the variable then use the variable name in the source object.

CodePudding user response：

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.