Solved

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

Posted on 2013-02-06
3
2,290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 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