?
Solved

How to Find list of database objects in SSIS package in Sql server 2008

Posted on 2013-02-06
3
Medium Priority
?
2,806 Views
Last Modified: 2016-02-10
Hi ,

I am new to SSIS  package ,we have  one of the jobs failed in sql server 2008 and this job is referring to  SSIS  package and this  package is referring  to 10 other packages .To find out what went wrong with the package ,

1) I need to find list of database objects (Stored proce,tables, views etc) in all 10 pakcages without manually going to each package.

2)How to debug a  SSIS pakcakage ?

3) In the above case, Do I need to debug all the packages ? Does the system maintain error log for SSIS packages?

Thanks in Advance
0
Comment
Question by:ksd123
3 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 38860455
In a SSIS Script Task,  add reference to the SMO objects, and then generate the script of a database object. We can then store the generated script in a writeable variable. Below are the general steps for your reference:
    1. Create a variable in the SSIS package.
    2. Use Script Task in the package.
    3. Double-click the Script Taks, set the variable created in step 1 to be writeable.
    4. Click "Edit Script".
    5. In the script design environment, add the following reference:
        Microsoft.SqlServer.Smo.dll
        Microsoft.SqlServer.ConnectInfo.dll
 
    ' TODO: Add your code here
Dim theServer As New Server(".")
Dim myDB As Database = theServer.Databases("AdventureWorks")

For Each tbl In myDB.Tables
       MsgBox tbl.Name
Next
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1500 total points
ID: 38862112
#2How to debug a  SSIS pack ?

Debugging Packages
http://msdn.microsoft.com/en-us/library/ms140057(v=sql.105).aspx

Debugging How-to Topics (Integration Services)
http://msdn.microsoft.com/en-us/library/ms137880(v=sql.105).aspx

---
#11) I need to find list of database objects (Stored proce,tables, views etc) in all 10 pakcages without manually going to each package.

Be ready to do it manually:

just in case check:

Find out the objects(including SSIS packages) dependent on Table
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/dd12ce2a-fbfe-450c-8c23-6d214c770383/
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38862205
not sure on how to limit to within a SSIS package, but this might be useful?

select 
  sao.type_desc collate SQL_Latin1_General_CP1_CI_AS as object_type 
, sao.name      collate SQL_Latin1_General_CP1_CI_AS as object_name 
from sys.all_objects as sao       /* sys.all_objects ! */
and sao.schema_id = SCHEMA_ID()   /* only in THIS schema */

Open in new window

or this forum post on social.msdn.microsoft.com
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question