Solved

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

Posted on 2013-02-06
3
1,991 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now