Calling a SSIS from SQL SP

Question

Post reply

Calling a SSIS from SQL SP

Soorya

Ten Centuries

Points: 1314
More actions
June 3, 2008 at 6:11 am

#380904

Hi,
I tried calling a SSIS package from a SQL stored proc. This SSIS package worked fine when run from visual studio and also in the command prompt using the dtexec. But when i tried to exectue the same SSIS from a SQL SP, i get the following error msg
Error: 2008-06-03 17:34:23.08
Code: 0xC0202009
Source: Pickup Bridge Connection manager "EBMaster.EBProd"
Description: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'ITLINFOSYS\CHNMCT90315$'.".
End Error
Here, ITLINFOSYS is my Domain and the CHNMCT90315 happens to be the server where i am executing this SP.
1. Why is my machine name replacing my login name
2. Why do i get this error only when i try to execute the SSIS package from a SP and not from VisualStudio/cmd prompt.
I am new to this SSIS stuff and i'm stuck with this.
Thanks in advance for the suggestions

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Michael Earl-395764

SSC Guru

Points: 53873

June 3, 2008 at 6:35 am

#823558

How are you running the package, using xp_CmdShell?

Soorya Ten Centuries Points: 1314 More actions · Answer 2

Yes. i am using xp_cmdshell to call the package.

DECLARE

@BranchIDINT

,@LOBIDINT

--Variable Declaration Section

DECLARE@CALLnVARCHAR(2000)

SELECT @BranchID = 28, @LOBID = 1

--Interface call

SET @CALL ='dtexec /SQL "\PickupBridge" /SERVER CHNMCT90315 /SET \package.variables[BranchID].Value;"'+CONVERT(varchar(16),@BranchID)+'" /SET \package.variables[LOBID].Value;"'+CONVERT(varchar(16),@LOBID)+'"'

EXEC MASTER..xp_cmdshell @Call

Soorya Ten Centuries Points: 1314 More actions · Answer 3

Also, when i tried to run the packaged using xp_cmdShell after switching the connection strings from WIndows Authentication to SQL server authentication (i changed the authentication as i was getting a login failure with the other option), i get the following error msg Description: Failed to decrypt protected XML node "DTS-Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that

Paul Ingham SSC-Addicted Points: 424 More actions · Answer 4

Hi

I had a similar problem and used the following code to get around it.

DECLARE @cmd VARCHAR(255)

DECLARE @Wait INT

DECLARE @result INT, @OLEResult INT

DECLARE @ShellID INT

SET @Wait = 0

SET @cmd = 'DTEXEC /f "\\ "

-- create instance of OLE object

EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT

IF @OLEResult <> 0 SELECT @result = @OLEResult

IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)

-- run package

EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', NULL, @cmd, 0, @Wait

IF @OLEResult <> 0 SELECT @result = @OLEResult

IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)

If @OLEResult <> 0 EXEC sp_OAGetErrorInfo @ShellID, @OLEResult

-- drop object

EXECUTE @OLEResult = sp_OADestroy @ShellID

Soorya Ten Centuries Points: 1314 More actions · Answer 5

Hi Paul,

Sorry to say taht I am not able to overcome my prob with the code snippet u've attached. When i try to execute the code, the package is not getting triggered. Could you pls help me with this? :crying:

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

When you run xp_cmdshell, it runs in the context of the MSSQL service account (or a proxy if you have it configured), not the login you have used to connect to the sql server. Make sure your SQL service account has the appropriate permissions.

If I were you, I would not go about this in the way you are trying to. xp_Cmdshell is disabled by default for a reason, it is a big security problem. I would create a SQL Agent job that executes the package and use sp_start_job to execute it. If you need the procedure calling the SSIS package to wait for the package to complete, use a loop in your procedure and check the status of the job. Using this approach will not only allow you to disable xp_cmdshell again, but it will also give you a log each time the package is run and make it easy to manipulate settings, configurations, and variables by modifying the SQL Agent job.

Soorya Ten Centuries Points: 1314 More actions · Answer 7

Thanks for your suggestion Michael. We are not in a position to go for jobs right now as it would involve a lot of other reworks. We solved the problem in calling the SSIS package from the SP by creating a new SQL login and using a DTSConfig file for the package. But, we face a new issue now. We face no problem if the package and the SP reside in the same server. But we face the same AcquireConnection problem when the package and SP reside in different servers.

Thanks in advance for any help.