running dts packages inside stored procedure

  • Hi,

    we have a stored procedure that moves data from sqlserver1 to sqlserver2. Both sqlserver1 and sqlserver2 are running sql server 2000. Inside the stored procedure I am running a dts package that moves the data. Everything is working fine till now. Now we have migrated sqlserver1 to Sql Server 2005. Now when I run the dts package I am getting an error.

    Microsoft (R) SQL Server Execute Package Utility

    Version 9.00.1399.06 for 64-bit

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    NULL

    Started: 9:17:52 AM

    Could not load package "MyDataLoad" because of error 0xC001000A.

    Description: The specified package could not be loaded from the SQL Server database.

    Source:

    Started: 9:17:52 AM

    Finished: 9:17:53 AM

    Elapsed: 0.484 seconds

    NULL

    Following is the command I used to run the package

    declare @DTSRUNCommand varchar(255)

    set @DTSRUNCommand =

    'DTexec /SQL ' +

    'MyDataLoad /Server ' +

    @@servername

    exec master..xp_cmdshell @DTSRUNCommand

    Please let me know how I could fix this?

    Thanks,

    sridhar.

  • Try inserting

    PRINT @DTSRUNCommand

    in the stored procedure, use the output from it to run it manually on the server to see what happens?

    Dave

  • I got it working. I have used the migration tool to convert the dts package to sql server 2005 format. Once I did that it worked fine.

    Thanks,

    sridhar.

Viewing 3 posts - 1 through 2 (of 2 total)

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