<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
SSIS Script task to change table name in data...
12 posts, Page 1 of 2
1
2
»»
SSIS Script task to change table name in data flow task
Rate Topic
Display Mode
Topic Options
Author
Message
Hardy21
Hardy21
Posted Sunday, January 13, 2013 5:00 PM
SSC Eights!
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877,
Visits: 1,158
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
Phil Parkin
Phil Parkin
Posted Monday, January 14, 2013 5:09 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226,
Visits: 9,458
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1406662
Hardy21
Hardy21
Posted Monday, January 14, 2013 5:11 AM
SSC Eights!
Group: General Forum Members
Last Login: Monday, May 13, 2013 2:42 PM
Points: 877,
Visits: 1,158
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
Phil Parkin
Phil Parkin
Posted Monday, January 14, 2013 5:35 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226,
Visits: 9,458
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1406670
kenambrose
kenambrose
Posted Monday, January 14, 2013 8:42 AM
Valued Member
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:13 AM
Points: 70,
Visits: 413
[[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
Phil Parkin
Phil Parkin
Posted Monday, January 14, 2013 9:09 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226,
Visits: 9,458
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1406791
kenambrose
kenambrose
Posted Monday, January 14, 2013 8:56 PM
Valued Member
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:13 AM
Points: 70,
Visits: 413
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
(
8 views,
172.93 KB
)
Post #1407012
kenambrose
kenambrose
Posted Monday, January 14, 2013 9:45 PM
Valued Member
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:13 AM
Points: 70,
Visits: 413
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
Phil Parkin
Phil Parkin
Posted Monday, January 14, 2013 11:34 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: 2 days ago @ 5:12 AM
Points: 4,226,
Visits: 9,458
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:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.
Post #1407042
kenambrose
kenambrose
Posted Tuesday, January 15, 2013 6:27 PM
Valued Member
Group: General Forum Members
Last Login: Monday, May 13, 2013 8:13 AM
Points: 70,
Visits: 413
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 »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
