SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calling SSIS from Stored Procedure


Calling SSIS from Stored Procedure

Author
Message
Mohan.deval
Mohan.deval
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 104
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 "<DTSTongueroperty xmlnsBigGrinTS="www.microsoft.com/SqlServer/Dts" DTS:Name="PackageFormatVersion">3</DTSTongueroperty>" from node "DTSTongueroperty".
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



Mohan.deval
Mohan.deval
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 104
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search