SSIS Script task to change table name in data flow task

Question

SSIS Script task to change table name in data flow task

Hardy21

SSCrazy Eights

Points: 9708
More actions
January 13, 2013 at 5:00 pm

#393928

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

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kenambrose SSCrazy Points: 2513 More actions · Answer 4

[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.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kenambrose SSCrazy Points: 2513 More actions · Answer 6

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.

kenambrose SSCrazy Points: 2513 More actions · Answer 7

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.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 8

kenambrose (1/14/2013)
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.

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kenambrose SSCrazy Points: 2513 More actions · Answer 9

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...

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

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

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 11

I am able to figure out using 'TaskHost' object.

Thanks for your response.

Thanks