SSIS Script task to change table name in data flow task
-
Hello,
We need to change the table name of 'OLE DB Destination' task.
I am not able to use variable due to there are almost 20 data flow tasks in SSIS package and I need to use more variables.
Is it possible to update table name using SSIS Script task?
If yes, how can I use SSIS Script task to update the table name?
I am really thankful if you help me to resolve the issue.
Thanks
-
Do your various destination tables contain different column definitions, or do they all have exactly the same structure?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil Parkin (1/14/2013)
Do your various destination tables contain different column definitions, or do they all have exactly the same structure?Table structure is same - columns, data types are same.
For example: TableA & TableAa has same structures (columns, data types are same). TableA needs to replace with TableAa, same way TableB needs to replace with TableBb.
Thanks
-
There's nothing easy.
But if you don't mind writing some code, you may find that a Script Component Destination gives you the flexibility that you need. Have a look here.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
[I am not able to use variable due to there are almost 20 data flow tasks in SSIS package and I need to use more variables.]
Did you consider to use variable scoped to the task level? Thats what I do in ths same situation.
-
kenambrose (1/14/2013)
[I am not able to use variable due to there are almost 20 data flow tasks in SSIS package and I need to use more variables.]Did you consider to use variable scoped to the task level? Thats what I do in ths same situation.
How can you use a variable to switch a destination table name?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
If using Execute Sql task, make the sql text an expression and use variables for table names
If using oledb destination in data flow task, the data access mode property of the connection property allows you to use a variable tablename for the destination. see screen shot example.
There are a few tricks to doing this I found. The target table you point to in the IDE at design time must actually exist at design time. And of course each data flow can only support one data structure, although the target tablename can be dynamic at run time, the structure of the table cannot.
-
I attached a screen shot of one way to have variables for object names for the OP, I am sure you already know how to do this for an ExecuteSql task.
-
kenambrose (1/14/2013)
If using Execute Sql task, make the sql text an expression and use variables for table namesIf using oledb destination in data flow task, the data access mode property of the connection property allows you to use a variable tablename for the destination. see screen shot example.
There are a few tricks to doing this I found. The target table you point to in the IDE at design time must actually exist at design time. And of course each data flow can only support one data structure, although the target tablename can be dynamic at run time, the structure of the table cannot.
Excellent - I'd forgotten about that option. Sounds like exactly what the poster needs.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
And actually, each target table must exist before the oledb destination with that table name fires up. However if needed, in the same "for each" loop a simple precedent step to the oledb destination step can check for existence of current tablename and create it if not found...
The real limitation is that oledb destination can't support dynamic data structure in addition to source and target tablenames- or at least I can't figure out how to do that...
-
kenambrose (1/15/2013)
The real limitation is that oledb destination can't support dynamic data structure in addition to source and target tablenames- or at least I can't figure out how to do that...My thinking around this starts with building and executing a Package programmatically within the context of the already running package. In looking into it a little for SSIS 2012 it looks possible from within a Script Task but it would turn out to be quite an elaborate set of code to manage there. For SSIS 2012 trying it in a Script Task would require referencing assemblies made available by installing the SSIS 2012 "Client Tools SDK" available through the SQL Server installer.
If it could not be achieved within a Script Task then I am confident it can be accomplished by building and rolling your own custom SSIS Component developed in Visual Studio. If you went for a custom component I am sure you could produce a separate version of it for each version of SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I am able to figure out using 'TaskHost' object.
Thanks for your response.
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply