Developing Matt

My Technical Journal

DTS Package Explore

leave a comment »

With the help of Gregory Larsen in this article I came up with a script that explores DTS package properties on a server.  I needed to know what dts packages still sit out on these servers to know how big of an SSIS migration I face.  You can get certain information from sysdtspackages, sysdtspackagelog, sysdtssteplog, and even the undocumented sp_enum_dtspackages procedure (all found in the msdb), but it doesn’t give you all the information that I wanted.  Mr. Larsen, with his article, showed me how to open up each package using sp_OACreate and retrieve, and even modify, it’s contents.  Specifically, he used it back in 2005 to update connection strings.  I was just wanting to read the information to find out what packages touch what servers, etc.  So I tweaked his code a bit, took the updates out, and ran it against my servers to determine what packages I have, what connections they use, what tasks they implement, and the steps.  Thanks for pointing me in the right direction.

TOO MUCH FUN!

use
msdb
 

 

DECLARE
@SERVER VARCHAR(30)
 

 

SET
@SERVER =

@@SERVERNAME 
 
— Get list of Packages from KB article 241249

 

 
–drop table #dts_packages drop table #pInfo
 

 

create
table#dts_packages
 

 

 
(name varchar(1000),
 

 

id
uniqueidentifier,
 

 

versionid
uniqueidentifier,
 

 

 
description varchar(1000),
 

 

createdate
datetime,
 

 

 
owner varchar(100),
 

 

size
int,
 

 

packagedata
image,

isowner
 
varchar(100),

 

packagetype
int
 

 

 
)
 

 

 

Create
table#pInfo (
 

 

PackageName
varchar(1000),
 

 

PackageOwner
varchar(50),
 

 

CreateDate
varchar(50),
 

 

Size
varchar(50),
 

 

PackageDetailItem
varchar(1000),
 

 

NumberOF
varchar(30),
 

 

DetailName
varchar(1000),

Detail
 
varchar(1000)

 

 
)
 

 

insert
into#dts_packages execmsdb..sp_enum_dtspackages
 

 

———————
— Begin Section A —
———————

set
nocount on
 

 

–Declaring variable

DECLARE
@object

intDECLARE
 
@pkgname nvarchar(255)

 

declare
@rc

intDECLARE
 
@src varchar(255)

 

Declare
@desc varchar(255)
 

 

Declare
@Numof

intDeclare
 
@NumofItems

 

intdeclare
 
@i

 

intdeclare
 
@j

 

intDeclare
 
@property varchar(8000)

 

Declare
@property_value varchar(8000)
 

 

Declare
@property_value2 varchar(8000)
 

 

Declare
@property_name varchar(8000)
 

 

——————-
— End Section A —
——————-

———————

— Begin Section B LOAD PACKAGE–

———————

while
(select count(*) from#dts_packages) > 0
 

 

begin

 
select top1 @pkgname=name from#dts_packages order by

name 
 
insert into

 

#pInfo 

 
 
select top 1 name, owner,createdate, size, , , ,

 

 
 
from#dts_packages order by

 

name 

 
 
delete from#dts_packages where name =

 

@pkgname 
 
Print ‘Starting the inspection of package ‘ + rtrim(@pkgname)

 

–Creating object

 
EXEC@rc = sp_OACreate ‘DTS.Package’, @object OUTPUT
 

 

 
IF@rc <> 0 goto PrintError
 

 

— Load Package from Source Server

 
EXEC@rc = sp_OAMethod @object, ‘LoadFromSQLServer’,
 

 

— SQL Server Authentication
— NULL,’SERVER1′,’login’,’password’,’0′,”,”,”,@pkgname
— Windows Authentication

 
NULL,@server,,,‘256’,,,,

@pkgname 

 
 
IF@rc <> 0 goto

 

PrintError 
 
print ‘Package loaded successfully’

 

——————-
— End Section B —
——————-

———————

— Begin Section C CONNECTIONS–

———————

— Get Number of Connections

 
EXEC@rc = sp_OAGetProperty @object, ‘Connections.Count’,@Numof OUT
 

 

 
IF@rc <> 0 goto

PrintError 
 
insert into#pInfo select , , , ,‘Connections’,@Numof, ,

 

” 
 
 

 

 
set @i = 0
 

 

— Process Through each Connection

 
While @i <

@Numof  
 
begin

 

 
 
set @i = @i + 1

 

— Get Name of Connections

 
set @property = ‘Connections(‘ + rtrim(cast(@i as char)) + ‘).Name’
 

 

 
EXEC@rc = sp_OAGetProperty @object, @property, @property_value OUT
 

 

 
IF@rc <> 0 goto PrintError
 

 

— Get DataSource of Connections

 
set @property = ‘Connections(‘ + rtrim(cast(@i as char)) +

 
 
‘).DataSource’

 

 
EXEC@rc = sp_OAGetProperty @object, @property, @property_value2 OUT
 

 

 
IF@rc <> 0 goto

PrintError 

 
 
insert into#pInfo select , , , ,‘Connections’,, @property_value,

 

@property_value2 
 
end

 

 
 

end

select
* from#pInfo
 

 

— Process Errors

PrintError:

EXEC
sp_OAGetErrorInfo @object,@src OUT,@desc OUT
SELECT rc=convert(varbinary(4),@rc), Source =@src, Description =@desc

 

——————-
— End Section F —
——————-

 

Advertisements

Written by matt

January 13, 2009 at 2:14 pm

Posted in Sql Server, SSIS, T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: