SSIS/SSAS: How to change only the table name without remap the columns, flows and dimensions?

Luis Lima 61 Reputation points
2020-09-01T13:36:21.613+00:00

Hi,

My company asked me to change the name of some of my SQL Tables( Dimensions ). But the only way that I know to do that is delete the references to the old table and map them again and recreate the OleDBSource/OleDBDestination ( SSIS ) and recreate the dimensions with the attributes and hierarchies ( SSAS ).

I need to change only the table names, the columns will still be the same. What I want is somehow only change the table name, but without change my flows, remap all my columns, dimensions, hierarchies, ....

1- Is it possible in SSIS?

2- Is it possible in SSAS?

Best Regards,
Luis

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
0 comments
Accepted answer
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-02T02:42:25.1+00:00

    Hi Luis,

    1- Is it possible in SSIS?

    Yes, it's possible.

    1.Please create a variable User::TableName to store the sql table name
    2.Then we should choose Data access mode as Table name or view name variable in the OLEDB Source/Destination Editor.
    3.If the structures(columns) of the tables are same, we can just change the value of the variable User::TableName to change the table in the OLEDB Source/Destination.

    Please refer to the following pictures:
    22097-twosqltable.png
    22098-data-access-mode-table-name-or-view-name-variable.png
    22043-mytable2.png
    22022-mytable.png

    Best Regards,
    Mona

    If the response is helpful, please click "Accept Answer" and upvote it.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-09-02T02:45:27.9+00:00

    Hi,
    Where did you need to change the name ?

    If your only need to change the table name in SSAS Cube, or the name in Excel when we browse the cube , you could only directly change the (dimension table) name in Cube design page in VS - SSDT SSAS project. This would change the whole name in relationships and hierarchy for you.

    But if you need to change the table name in your SQL Server Database, you would need to refresh the reference name(by replace the old table with the new named table) in SSAS DSV.

    Regards,
    Lukas

    0 comments
  2. Olaf Helper 41,001 Reputation points
    2020-09-03T11:38:02.753+00:00

    Create views with old table name with a simple SELECT statement on the new table name; then you don't break your SSIS & SSAS solutions.

    Olaf

    0 comments
  3. Luis Lima 61 Reputation points
    2020-09-03T17:33:15.437+00:00

    Hi,

    Thank You for all the comments! I tried to to Accept the 3 Answers but sadly the forum only accept 1 flag.
    All the 3 answers helped me.

    Best Regards,
    Luis