I need to find the name of DTS which has a SP into a sql task????

Question

I need to find the name of DTS which has a SP into a sql task????

Hector Angello Silva Carmona

SSCrazy

Points: 2720
More actions
July 5, 2005 at 12:57 pm

#76365

I need to find the name of DTS which a determinated SP is inside of a task.
for example search SP_Ineedtofindyou ......
task 1 of DTS DTS_Igotit has this SP....

some friends have some VBScript or something to do that?
Thank you a lot of ..............

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 1

You can use sp_OAGetProperty on the DTS object. You can use the Visual Studio object browser to get the list of object properties exposed.

Use sp_OACreate to create a DTS package object, then sp_OAMethod to retrieve it. After that, you can use sp_OAGetProperty until you find what you need.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 2

Interesting, friend can you give me an example?

the Visual Studio Object Browser is a Api of sqlserver? or i must to download of Microsoft???

thanks friend

Rick Lowrey Right there with Babe Points: 785 More actions · Answer 3

Here is a sample script to get all the connections and data sources from a DTS package. You can also declare a cursor on msdb.dbo.sysdtspackages to get the names of all the packages on a server.

create table #temp1

(

id int,

property varchar(255),

connectiontype varchar(255),

datasource varchar(255)

)

DECLARE @DTSPackageObject INT

DECLARE @Result INT

DECLARE @DataSource VARCHAR(255)

DECLARE @Property VARCHAR(255)

DECLARE @Looper INT

DECLARE @ConnectionType VARCHAR(255)

DECLARE @Connections int

DECLARE @SQLInstance VARCHAR(255)

DECLARE @PackageID INT

DECLARE @PackageName VARCHAR(255)

DECLARE @IsEncrypted BIT

DECLARE @Password VARCHAR(255)

set @packagename = 'MyDTSPackageName'

set @Looper = 1

EXEC @Result = sp_OACreate 'DTS.Package', @DTSPackageObject OUTPUT

EXEC @Result = sp_OAMethod 16711422, 'LoadFromSQLServer', NULL, 'MySQLInstance', null, null, 256, null, null, null, @PackageName

IF @Result = 0 -- Got Package Successfully

BEGIN

EXEC @Result = sp_OAGetProperty @DTSPackageObject, 'Connections.Count', @Connections OUT

WHILE @Looper <= @Connections

BEGIN

SELECT @Property = 'Connections(' + CAST(@Looper AS VARCHAR) + ').ProviderID'

EXEC @Result = sp_OAGetProperty @DTSPackageObject, @Property, @ConnectionType OUT

SELECT @Property = 'Connections(' + CAST(@Looper AS VARCHAR) + ').DataSource'

EXEC @Result = sp_OAGetProperty @DTSPackageObject, @Property, @DataSource OUT

insert into #temp1

(id, property, connectiontype, datasource)

select @Looper, @property, @connectiontype, @datasource

SELECT @Looper = @Looper + 1

END

end

select * from #temp1

drop table #temp1

Look in BOL for further explanations of all the parameters for the sp_OAxxx calls.

Visual Studio is the IDE for VB or VC++.

Hector Angello Silva Carmona SSCrazy Points: 2720 More actions · Answer 4

Thanks another friend posted a VBscript too, thanks in deep, you have helped to me a lot of

leoinfo SSC Rookie Points: 34 More actions · Answer 5

Did someone get this working on SQL2005 for SSIS packages?

Is there any way to load and run a SSIS from a stored procedure?