SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Script task to change table name in data flow task


SSIS Script task to change table name in data flow task

Author
Message
Hardy21
Hardy21
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4016 Visits: 1399
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
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86518 Visits: 21739
Do your various destination tables contain different column definitions, or do they all have exactly the same structure?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Hardy21
Hardy21
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4016 Visits: 1399
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
Phil Parkin
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86518 Visits: 21739
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kenambrose
kenambrose
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 665
[[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
Phil Parkin
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86518 Visits: 21739
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kenambrose
kenambrose
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 665
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.



Attachments
kenambrose
kenambrose
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 665
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.



Attachments
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86518 Visits: 21739
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
kenambrose
kenambrose
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1057 Visits: 665
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...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search