1

Topic: Start of a dtsx-package with the help xp_cmdshell

Good afternoon!
I try to organize loading of the data in basis from the indirect server.
Created a DTSX-package.
If I launch a packet manually from file system the data are loaded.
If I try to launch the same packet with the help there is an error.
I ask how to organize loading of the data for suggestions.
As I try to make:
Packet Start:
DECLARE @returncode int
EXEC @returncode = xp_cmdshell ' dtexec/f "C:\Projects\MOHSPP\MOHSPP\MOHSPP\Package.dtsx"'
select @returncode
The Error report:
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.6000.29 for 64-bit
Copyright (C) OF MICROSOFT CORP 1984-2005. All rights reserved.
NULL
Started: 9:07:33 AM
Error: 2017-12-05 09:07:38.11
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "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 the
correct key is available.
End Error
Progress: 2017-12-05 09:07:40.70
Source: MOHSPP
Validating: 0 % complete
End Progress
Error: 2017-12-05 09:07:42.30
Code: 0xC0047062
Source: MOHSPP SAP BI Source [1]
Description: System. BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B)
at Microsoft.SqlServer.Dts.SapBw.Connectivity.RFCAPI.RfcOpenEx (String conn_param, RFC_ERROR_INFO_EX& error_info)
at Microsoft.SqlServer.Dts.SapBw.Connectivity.R3Connection.Open (String ConnectionString)
at Microsoft.SqlServer.Dts.SapBw.ConnectionManager.SapBwConnectionManager.AcquireConnection (Object txn)
at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection (Object txn)
at Microsoft.SqlServer.Dts.SapBw.Components.SapBwPipelineComponent.AcquireConnections (Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections (IDTSManagedComponentWrapper100 wrapper, Object transaction)
End Error
Error: 2017-12-05 09:07:42.33
Code: 0xC0047017
Source: MOHSPP SSIS.Pipeline
Description: component "SAP BI Source" (1) failed validation and returned error code 0x8007000B.
End Error
Progress: 2017-12-05 09:07:42.33
Source: MOHSPP
Validating: 50 % complete
End Progress
Error: 2017-12-05 09:07:42.33
Code: 0xC004700C
Source: MOHSPP SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2017-12-05 09:07:42.35
Code: 0xC0024107
Source: MOHSPP
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:07:33 AM
Finished: 9:07:42 AM
Elapsed: 8.767 seconds
NULL
Server options:
MS A SQL server 2008
Microsoft SQL Server Management Studio 10.0.6535.0
Microsoft Analysis Services Client Tools 10.0.6000.29
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.10.9200.16576
. NET FRAMEWORK Microsoft 2.0.50727.8762
Operating System 6.1.7601
WINDOWS Server 2008 R2 Enterprise SP1

2

Re: Start of a dtsx-package with the help xp_cmdshell

In an error all is written, why it is not launched.

3

Re: Start of a dtsx-package with the help xp_cmdshell

Probably, incorrectly formulated the question.
How to avoid an error?

4

Re: Start of a dtsx-package with the help xp_cmdshell

OKoryagina;

wrote:

One or more component failed validation.

Repair either a packet or basis. Objects of basis do not correspond to packet waitings.

5

Re: Start of a dtsx-package with the help xp_cmdshell

OKoryagina;
Here it

Error: 2017-12-05 09:07:38.11
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "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 the
correct key is available.

that connection passwords are specified in Connection Manager and ciphered SSIS - standard practice. When you launch a packet under yourselves, passwords  is normal, under other user - there will be an error. Particulars - sm documentation MS .
Candidate solutions:
If at you SSIS 2012 and above - use projects SSIS, Connection Manager the project and at  the project - set environment where the password is stored. The special representative the person can enter the most correct decision, the password and the password is ciphered.
To install parameter of saving of passwords in packet Protection Level = EncryptSensitiveWithPassword and to specify the enciphering password. Further, at packet start it is necessary to specify this password.
Following errors - grow from impossibility of connection.

6

Re: Start of a dtsx-package with the help xp_cmdshell

Ferdipux;
Thanks for the answer, whether correctly I understand, what the password is necessary for entering hand to hand?
The purpose - to organize loading of the data without involvement of the person.
On the server it is torn
Microsoft Visual Studio 2008
Version 9.0.30729.1 SP
. NET FRAMEWORK Microsoft
Version 3.5 SP1

7

Re: Start of a dtsx-package with the help xp_cmdshell

OKoryagina;
If a Visual Studio 2008, it is possible to assume as the server 2008. Though not the fact.
If the server "old" (2008 and earlier) and you launch a packet from a file - that at you 2 ways. To cipher a packet with the password and to feed the password at start dtexec with parameter/Decrypt <password>. Or - at start - to substitute new ConnectionString in which to specify the password - parameter/Connection and line of connection.
Look help on dtexec behind particulars.

8

Re: Start of a dtsx-package with the help xp_cmdshell

Ferdipux;
Thanks, I will try.
The server really 2008.