Solved

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

Posted on 2013-02-06
3
2,101 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 42

Accepted Solution

by:
EugeneZ earned 500 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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

770 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