Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS Script task to change table name in data flow task Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 5:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
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
Post #1406532
Posted Monday, January 14, 2013 5:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 4,831, Visits: 11,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1406662
Posted Monday, January 14, 2013 5:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
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
Post #1406664
Posted Monday, January 14, 2013 5:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 4,831, Visits: 11,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1406670
Posted Monday, January 14, 2013 8:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:10 PM
Points: 71, Visits: 472
[[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.



Post #1406774
Posted Monday, January 14, 2013 9:09 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 4,831, Visits: 11,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1406791
Posted Monday, January 14, 2013 8:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:10 PM
Points: 71, Visits: 472
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.





  Post Attachments 
ExampleOleDbDestinationVariableTarget.jpg (9 views, 172.93 KB)
Post #1407012
Posted Monday, January 14, 2013 9:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:10 PM
Points: 71, Visits: 472
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.




  Post Attachments 
ExampleSqlStatementSourceExpression.jpg (5 views, 130.20 KB)
Post #1407017
Posted Monday, January 14, 2013 11:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 4,831, Visits: 11,193
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1407042
Posted Tuesday, January 15, 2013 6:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:10 PM
Points: 71, Visits: 472
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...






Post #1407537
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse