SSIS Script task to change table name in data flow task

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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

    SSC Guru

    Points: 244733

    Do your various destination tables contain different column definitions, or do they all have exactly the same structure?

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hardy21

    SSCrazy Eights

    Points: 9708

    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: 244733

    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.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kenambrose

    SSCrazy

    Points: 2483

    [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: 244733

    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?

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kenambrose

    SSCrazy

    Points: 2483

    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: 2483

    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: 244733

    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.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kenambrose

    SSCrazy

    Points: 2483

    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: 182269

    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

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

    Thanks for your response.

    Thanks

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

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