Package dependency SSIS

Keny Maciel 1 Reputation point
2020-09-08T14:27:21.427+00:00

Hi,

Developers constantly ask for changes to table structures (column name, PK, FK) in SQL Server.

I need to know which packages of the SSIS project should be changed if any database table is changed

There are more than 100 packages I can't do it manually, because it will take too long.

Any suggestion?
Thank you.

_Keny

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,504 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-09T01:39:24.987+00:00

    Hi @kenyMacielDaSilva-7534,

    SSIS is strongly types about metadata.
    For example: you create a simple dataflow with a source, derived column and destination. Change something in the destination table: package fails. Change something in the source? Package fails.     Every change in the metadata makes you re-opening all the components, so that the metadata can be refreshed. So also the derived column. Metadata changes do not propagate through the dataflow. This has the following important consequence: you cannot make the SSIS dataflow dynamic easily (at least not out of the box). You cannot dynamically change destinations or sources, unless they have the exact same metadata structure. If you load flat files with the exact same number of columns and datatypes with the same header, it will work. If one column is missing or there is an extra column, your package will fail.

    Please refer to meta data in ssis and Data Flow.

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. Olaf Helper 42,841 Reputation points
    2020-09-09T08:57:47.85+00:00

    SSIS packages are simple XML file; you can open them in a text editor and search for the table names.

    0 comments
  3. Data Learner 1 Reputation point
    2020-09-21T15:44:18.527+00:00

    Just in case if you store your packages in MSDB DB, you can use dbo.sysssispackages dmv to get the table information.

    If not then you will have to open the package as XML code and ctrl+F to find the table name, and yes this would have to be done manually.

    0 comments