Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2908
  • Last Modified:

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

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
ksd123
Asked:
ksd123
1 Solution
 
Barry CunneyCommented:
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
 
Eugene ZCommented:
#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
 
PaulCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now