Calling SSIS from Stored Procedure

  • Hi,

    Our server has SQl 2008 & SQl 2005 Side by side installation. I am able to execute a SSIS Package that executes as a job & Within BIDS successfully. The issue is when I try to execute it from a Stored procedure

    declare @cmd varchar(1000)

    Declare @FromDate varchar(20)

    Declare @ToDate varchar(20)

    select @cmd = 'dtexec /F "' + @ssispath + '"'

    select @cmd = @cmd + ' /SET \Package.Variables[User::FromDate].Properties[Value];"' + @FromDate + '"'

    select @cmd = @cmd + ' /SET \Package.Variables[User::ToDate].Properties[Value];"' + @ToDate + '"'

    EXEC sp_configure 'xp_cmdshell', '1' --- Enable Command Shell

    RECONFIGURE

    exec master..xp_cmdshell @cmd

    EXEC sp_configure 'xp_cmdshell', '0' --- Disable Command Shell

    RECONFIGURE

    I get the following error. Not sure how to target for SQl 2008

    ( Microsoft (R) SQL Server Execute Package Utility Version 10 ? for 32-bit) that my package is looking for, I mean to say the wrong DTExec being executed ? How can I give correct dtexec.exe

    path in command Shell @Cmd ? Please help.

    output

    Microsoft (R) SQL Server Execute Package Utility

    Version 9.00.1399.06 for 32-bit

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

    NULL

    Started: 5:32:33 PM

    Error: 2011-01-05 17:32:33.24

    Code: 0xC001700A

    Source:

    Description: The version number in the package is not valid. The version number cannot be greater than current version number.

    End Error

    Error: 2011-01-05 17:32:33.24

    Code: 0xC0016020

    Source:

    Description: Package migration from version 3 to version 2 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".

    End Error

    Error: 2011-01-05 17:32:33.24

    Code: 0xC0010018

    Source:

    Description: Error loading value "<DTS:Property xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:Name="PackageFormatVersion">3</DTS:Property>" from node "DTS:Property".

    End Error

    Could not load package "D:\SSIS Migrated packages\SetupUpTimeReport\SetupUpTimeReport\SetUpTimeReport.dtsx" because of error 0xC0010014.

    Description: The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encoun

    ter errors.". This occurs when CPackage::LoadFromXML fails.

    Source:

    Started: 5:32:33 PM

    Finished: 5:32:33 PM

    Elapsed: 0.11 seconds

    NULL

  • VioLA.....

    Rename the old exe in the SQL 2005 path to a different name (Example:- C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC_Old.exe)

    and works !

    Hope this helps someone...

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

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