Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calling SSIS from Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, January 5, 2011 3:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 8:02 AM
Points: 25, Visits: 99
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


Post #1043393
Posted Wednesday, January 5, 2011 4:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 8:02 AM
Points: 25, Visits: 99
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...


Post #1043401
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse