CODEWITHSUNDEEP
sqlsql-server-2008-r2dts
Kamran Pervaiz
Kamran Pervaiz

Reputation: 1931

Creating DTS package in SQL server 2008?

I am using below script to create DTS package and it is working fine on SQL 2000 but its failing on the first step "failed to create package..." on SQL Server 2008 R2. please help if someone knows the answer

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

actual query

print 'creating a DTS package'

DECLARE @objPackage int
DECLARE @returnValue int
EXEC @returnValue = sp_OACreate 'DTS.Package', @objPackage output
IF @returnValue <> 0
BEGIN
    raiserror('failed to create package returnValue = %d', 16, -1, @returnValue)
    return
END
EXEC @returnValue = sp_OAMethod @objPackage, 'LoadFromStorageFile' , null,
    @UncFile = @FileName, @password = null
IF @returnValue <> 0
BEGIN
    raiserror('Custom Error fileName = %s , description = %s', 16, -1, @FileName, @Description)
    raiserror('failed to load package returnValue = %d, package = %s', 16, -1, @returnValue, @PackageName)
    return
END
EXEC @returnValue = sp_OAMethod @objPackage, 'SaveToSQLServerAs' , null,
    @NewName = @PackageName, @ServerName = @@ServerName, @Flags = 256
IF @returnValue <> 0
BEGIN
    raiserror('failed to save to SQL server returnValue = %d, package = %s', 16, -1, @returnValue, @PackageName)
    return
END

Upvotes: 0

Views: 3498

Answers (1)

HLGEM
HLGEM

Reputation: 96572

SQl Server 2008 does not use DTS pacakages. You should be creating SSIS packages.

Therre is some availablity to work with DTS in SQl Server 2008,m ,but it does not come out of teh box. I would under no circumstances use this to create new DTS pacakages but only to manage exisitng ones until they can be converted to SSIS. Our policy was that every time we had to make a change to a DTS pacakge, we converted it. But the link below may help you find teh toold you need to work with DTS in SQL Server 2008.

http://msdn.microsoft.com/en-us/library/ms143755(v=sql.105).aspx

Upvotes: 1

Related Questions