Find the Tables that are generated from a SSIS package

Question

Find the Tables that are generated from a SSIS package

SQL_Kills

Hall of Fame

Points: 3154
More actions
June 26, 2014 at 4:00 pm

#288016

Hi,
I have three databases and 40 tables within each database on my server, for each of the tables I want to know which SSIS package generates that table. Is there a script that can do this?
If the SSIS package does not create or populate a table on the server I then want to check if there is a stored procedure that populates this.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

For the stored procedure part, you can query sys.sql_modules.

To find which packages touch which tables, you would have to parse the xml of each package or look at the package by hand.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

How would you parse the XML? Do you have any sql code that would do this? Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Here is a sample http://www.sqlservercentral.com/Forums/Topic1380488-21-1.aspx.

Are your packages stored in msdb or on disk?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 4

I am not quite sure? How would I know if the package is stored on the msdb or disk?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

You can try a query like what was in that article. If it returns results, then you have the packages stored in SQL Server (or at least some of them are).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 6

When I deploy a SSIS package I would do this in Visual Studio by clicking on File > Save Copy of

A new window would appear which would say "Save Copy of Package" with the following fields:

Package Location: SQL Server

Server: ServerName

Authentication type: Windows Authentication

Package path: /Package

Protection level: Rely on server storage and roles for access control