SSIS 2005: Dynamically create data flow dest table if it does not exist?
Hi all, I am looking for a way to leave a Data Flow Task destination table name as-is, and haveSSISauto-create the table if it doesn't exist already. I searched on this in the forums but based on the question it's difficult to kow if it has been answered or not. Details: I am writing some SSIS packages that need to be executable onanother server. Many of the Data Flow Tasks copy data (such as from a Fuzzy Grouping transformation, and lots of other stuff) into a new table. But the other server will not have these tables set up for the first run. My current solution is to check information_schema.tables and drop IF EXISTS. But, then the Data Flow Task will not work (becase table does not exist). So, I script to new window a create table statement based on the existing table that I use in my dev environment. This is a hack and I want to find a better method. It is quite possible (although unlikely) that the source columns could be changed in the future, or some query used to pull the data might be modified. If this happens, then I would need to change the CREATE TABLE Execute SQL task. I want my package to accommodate without having to modify it. When I use the Import/Export Wizard, I can select a table name from the drop down list OR type in a new name. When I type in the new name, it assumes I want to create the table. NOW, is there a way to mimic this in BI Developer Studio? Yep, I saved the Wizard version of the SSIS package and all it does is run a CREATE TABLE statement first. I am looking for a way to leave a Data Flow Task destination table name as-is, and haveSSISauto-create the table if it doesn't exist already. Any ideas? Brian Pulliam
September 20th, 2007 6:57pm

Brian, If the source columns change then you will need to change the dataflow, hence changing the Execute SQL Task is the least of your worries! Creating the table in an Execute SQL Task is absolutely the right way to go. If you only want to create it if it doesn't exist then just use this: if not exists (select 1 from sys.tables where name = 'table_name') begin CREATE TABLE table_name (...) end -Jamie
Free Windows Admin Tool Kit Click here and download it now
September 20th, 2007 7:24pm

Thanks for the speedy response Jamie. Yes if the source columns change it will need to be modified. However, if the data types change on existing columns, then it must be rewritten? That seems "sub-optimal". What about a way to programmatically run through the SQL query results and get their data types, then write a CREATE TABLE statement to support the results of that query, and then pump the data into the dest table? It seems pretty lame to have to use the results of the query (after running it) to create the table schema before running the query. Brian
September 20th, 2007 7:29pm

Dynamically creating a table is easy but there is little point. If the data-types of the table change then the dataflow will fail validation. This is by design by the way. -Jamie
Free Windows Admin Tool Kit Click here and download it now
September 20th, 2007 7:38pm

Thanks Jamie, it makes sense to design it in such a way so that the package jumps up and down if something changes. But I don't have to be happy about it. Grrr.
September 20th, 2007 7:50pm

FYI. The short answer to why its done that way is MAINLY because they want to eek out every last morsel of performance that they possibly can. Feel free to draw your own opinions as to whether they should have done or not. -Jamie
Free Windows Admin Tool Kit Click here and download it now
September 20th, 2007 11:48pm

I've tried the create table statement in an Execute SQL tast, but the package won't compile/validate because the metadata of the destination table can't be retreived in the Data Flow -- Destination step. Hmm....
May 13th, 2008 9:06pm

I have the same problem. First step is "Execute SQL Task" with CREATE TABLE inside. Second task is "Data Flow Task" - it should load data into table created in first step. But I receive Package Validation Error - The metadata for "[dbo].[TableCreatedInStepOne]" cannot be retreived. I'm seeking for solution right now...
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2008 12:18pm

Hubert Trzewik wrote: I have the same problem. First step is "Execute SQL Task" with CREATE TABLE inside. Second task is "Data Flow Task" - it should load data into table created in first step. But I receive Package Validation Error - The metadata for "[dbo].[TableCreatedInStepOne]" cannot be retreived. I'm seeking for solution right now... On the data flow task set DelayValidation=TRUE -Jamie
May 15th, 2008 12:30pm

Hi, Is there any way that i could create table dynamically using variable of foreach loop or something else in Execute SQL Task Kind regards, WAK.
Free Windows Admin Tool Kit Click here and download it now
September 19th, 2012 5:31pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics