Hi,
I have a standalone SSIS package that I wish to deploy from the file system, as opposed to SQL Server deployment.
Anyways, I was wondering if there's a command line utility for running SSIS packages on an ad hoc basis? What I was thinking was that I would put the call to the command line in a stored procedure using xp_cmdshell, so that the package can be called that way.
Am I thinking about this correctly?
Thanks
dtexec.exe is what you'll need to run from the command line.|||Thanks. Is this what people generally use for file system deployment?
Question:
Do I need to do anything special with the package first? Or just call it with dtexec.exe from wherever the pkg resides on the file system?
|||I guess I am wondering about protection levels... and how to work with protection levels when using file system deployment?
I find this all very confusing!
Are there any good articles that discuss all the in's and out's of proper pkg deployment?
|||Yes, dtexec is what is used to execute from the file system (and from the database). DTEXEC is the runtime engine for SSIS.
If you are running from the file system, I'd recommend setting the ProtectionLevel to SaveSensitiveWithPassword, and use the Decrypt switch to pass it to the package.
|||Ok thanks.
Q. Concerning "sensitive" data, I'm not 100% sure what constitutes sensitive data? FTP conn mgr passwords and OLE DB conn mgrs using SQL Server logins where you must specify the passwords?
Q. Another question: What if the OLE DB conn mgrs use Windows Authentication to connect to the db and someone else logs on to the server to run the package (and they don't have write permissions to the database)? Will this cause the conn mgr not to be able to connect t the db? In which case, even having a password won't help, right?
Q. But it seems that file system deployment forces the issue, so that I MUST set up a password if I am not deploying to SQL Server, correct?
Thanks much
|||Hi,
I set the encryption to "EncryptSensitiveWithPassword", and attempted to run this from Management Studio:
EXEC master..xp_cmdshell 'DTExec /FILE "D:\myPackage.dtsx" /DECRYPT "password"'
It appears to run, but these are some of the errors I get. It looks like both it doesn't like the key, and also that SSIS isn't properly installed on this server. Two separate issues, but how can I resolve the key problem first?
Thanks
output
NULL
(1 row(s) affected)
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: 7:56:53 PM
Error: 2007-06-18 19:56:53.89
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS
assword" 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
Error: 2007-06-18 19:56:54.56
Code: 0xC0202009
Source: myPackage Connection manager "myServer.myDB.mySQLServerLogin"
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 'mySQLServerLogin'.".
End Error
Error: 2007-06-18 19:56:54.58
Code: 0xC0012024
Source: Script Task
Description: The task "Script Task" cannot run on this edition of Integration Services. It requires a higher level edition.
End Error
Warning: 2007-06-18 19:56:54.58
Code: 0x80019002
Source: OnError
Description: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCoun
t or fix the errors.
End Warning
Error: 2007-06-18 19:56:54.58
Code: 0xC00291EC
|||It seems as though the passwords do not match. CaSE matters. And ensure they match. You did specify a password in the package properties box, right?|||You also have a permission issue to connect to the SQL Server DB. Make sure that whatever user is running the package has rights to connect to the SQL Server|||Yes, the password is set on the package. It's just "password", so it's no brainer.
So, Rafael, you're saying that the /Decrypt switch is not related to the rights to connect to the server?
I thought that having a password on the package would take care of all the access issues. That is, all the connections are already set in the package, so whoever runs it doesn't need to worry about the underlying connections?
So, how do I resolve this?
Basically, I am using a SQL Server user with read/write permissions in the database for all my OLE DB conn mgrs. Why does this connection fail when I use DTExec? What piece am I missing here?
Need help! Thanks.
|||Have you just tried running this on your own with dtexec on the command line? Without using xp_cmdshell?|||I think these are 2 separate issues. The package password will give the rights to read and execute whatever is inside of the package. I made my coment based on other error I see in the log:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".
It looks like the login credential to connect to that SQL Server are not right.
|||
Rafael Salas wrote:
I think these are 2 separate issues. The package password will give the rights to read and execute whatever is inside of the package. I made my coment based on other error I see in the log:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'mySQLServerLogin'.".
It looks like the login credential to connect to that SQL Server are not right.
But that would be because the sensitive information couldn't be decrypted, no?|||Sadie,
Try using EncryptAllWithPassword instead.|||
The package is set up with OLE DB conn mgrs that use SQL Server users.
When I run DTExec.exe, I am doing so as my Windows Login, which has admin rights on the server. I've never had permissions problems with the database.
No comments:
Post a Comment