SSIS package running independently but not in step of SQL Job
-
Hi,
I have created a SSIS package which contains OLE DB sources and OLE DB destinations and encrypted sensitive data with password.
When I run this package from visual studio it works fine.
Now i want to include this package as a step of SQL job.But after including it in a step the job fails at this step giving error:" Executed as user: INFML01011\SYSTEM. The package execution failed. The step failed."
What could b the reason and solution also :-)..
Regards
Abhay
-
secretshifter (6/12/2009)
Hi,I have created a SSIS package which contains OLE DB sources and OLE DB destinations and encrypted sensitive data with password.
When I run this package from visual studio it works fine.
Now i want to include this package as a step of SQL job.But after including it in a step the job fails at this step giving error:" Executed as user: INFML01011\SYSTEM. The package execution failed. The step failed."
What could b the reason and solution also :-)..
Regards
Abhay
What is the package ProtectionLevel that you use? The default option EncryptSensitiveWithUserKey doesn't work very well because you have use the same account that authored the package for execution in the SQL Job Agent. Try the option encrypt sensitive with password and include the decryption password to the command line used for executing the package in the job step.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Or alternativley, "DontSaveSensitiveData" and just run it under an account that has the rights to access everything that it needs.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (6/14/2009)
Or alternativley, "DontSaveSensitiveData" and just run it under an account that has the rights to access everything that it needs.This will not be enough. When you use this option, all passwords will be gone. You will have to use configuration file then to setup the package.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/14/2009)
RBarryYoung (6/14/2009)
Or alternativley, "DontSaveSensitiveData" and just run it under an account that has the rights to access everything that it needs.This will not be enough. When you use this option, all passwords will be gone. You will have to use configuration file then to setup the package.
Actually it will be enough, if it is done correctly. If you read the second half of my sentence: "run it under an account that has the rights to access everything that it needs." that should handle it. Most of the corporations that I work with do it this way.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (6/14/2009)
CozyRoc (6/14/2009)
RBarryYoung (6/14/2009)
Or alternativley, "DontSaveSensitiveData" and just run it under an account that has the rights to access everything that it needs.This will not be enough. When you use this option, all passwords will be gone. You will have to use configuration file then to setup the package.
Actually it will be enough, if it is done correctly. If you read the second half of my sentence: "run it under an account that has the rights to access everything that it needs." that should handle it. Most of the corporations that I work with do it this way.
I understand what you mean. But this will work only when using windows authentication. If you use a database, which doesn't support windows authentication then you have to think about configuration, etc.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/14/2009)
I understand what you mean. But this will work only when using windows authentication. If you use a database, which doesn't support windows authentication then you have to think about configuration, etc.Yes, but why would you assume otherwise? There's nothing in the OP that implies that, and if they aren't there's no reason to assume that they could not just enable it and Windows authentication is what Microsoft recommends.
IMHO, it's the best answer for a lot of reasons, why say that it won't work unless you have some information that supports that?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (6/14/2009)
CozyRoc (6/14/2009)
I understand what you mean. But this will work only when using windows authentication. If you use a database, which doesn't support windows authentication then you have to think about configuration, etc.Yes, but why would you assume otherwise? There's nothing in the OP that implies that, and if they aren't there's no reason to assume that they could not just enable it and Windows authentication is what Microsoft recommends.
IMHO, it's the best answer for a lot of reasons, why say that it won't work unless you have some information that supports that?
But there is nothing in the question, which implies Microsoft specific solution is seeked. I wanted to make clear your suggestion works well only for SQL Server database, etc. By not making this clear if someone else checks this discussion at later time , he will assume this solution works always. And that is not the case.
p.s.
If that was the best solution, why Microsoft bothered to implement the other options?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (6/14/2009)
RBarryYoung (6/14/2009)
CozyRoc (6/14/2009)
I understand what you mean. But this will work only when using windows authentication. If you use a database, which doesn't support windows authentication then you have to think about configuration, etc.Yes, but why would you assume otherwise? There's nothing in the OP that implies that, and if they aren't there's no reason to assume that they could not just enable it and Windows authentication is what Microsoft recommends.
IMHO, it's the best answer for a lot of reasons, why say that it won't work unless you have some information that supports that?
But there is nothing in the question, which implies Microsoft specific solution is seeked.
That would be true for 90% of the questions asked in all of the SQL Server forums in the world. It doesn't make it OK to just assume that they cannot use Microsoft authentication anymore than it would be OK to just assume that say, all OLTP questions here needed their SQL to run on both SQL Server and Orcale and then claim that other people's answers were wrong that assumption that is never explained.
I wanted to make clear your suggestion works well only for SQL Server database, etc.
This is a SQL Server forum. It seems pretty fair to assume that a question is about SQL Server unless they say otherwise. However if you had this concern, why didn't respond to my answer with "This will not be enough if you need to access data resources that do not work with Windows authentication."?
Instead of what you actually said about my answer which is "This will not be enough." No qualifications, no conditions about environment, no "it depends", you just told everyone that my solution would not work, though in fact it works perfectly most of the time that SSIS packages are used (about 80% of the time in my experience). And is the preferred solution in those cases because it is more reliable, more secure and it vastly reduces administrative overhead.
By not making this clear if someone else checks this discussion at later time , he will assume this solution works always. And that is not the case.
So instead you choose to leave them with the impression that the more secure and easier to use solution would never work for them? For the sake of a case that only happens a minority of the time?
I wasn't going to say anything before, but having gotten this far into it, I have to be honest here: Frankly, I am a little bit offended at how you replied to my answer. I didn't say anything in my answer to disrespect you or your prior answer, though because of the security issues and the it's greater difficulty, I certainly could have. Yet you choose to reply in a way that would give any uninformed reader the impression that my answer did not work, which is absolutely untrue.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
My friend,
No direspect, but you are wrong about the 90% argument. It is called "SQL Server Integration Services" platform for a reason. It does imply that it is expected to work well for other databases other than SQL Server, too. And not only that. The solution DontSaveSensitive doesn't work at all when connecting with FTP or other transfer protocols. And this is also part of SSIS. I understand you are building Microsoft-specific solutions for a living, but we also have customers and the picture is different compared to yours.
Having said that, you have to agree we are both right. I accept your correction regarding my original incomplete answer. And you have to agree your original answer was incomplete, too 😉
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Hi,
I have used protection level "Encrypt sensitive data with password"and set the job step type to "SQL Server Integration Services Package"...but i dont know how to include the decryption password to the command line used for executing the package in the job step as u suggested.
-
secretshifter (6/15/2009)
Hi,I have used protection level "Encrypt sensitive data with password"and set the job step type to "SQL Server Integration Services Package"...but i dont know how to include the decryption password to the command line used for executing the package in the job step as u suggested.
Change the job step execution type to OS CmdExec and then you will have the command line available to modify. Include "/DE " option to your command line.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
I always use "Save copy of package as" from BIDS/Visual Studio and save to the SQL Server (msdb) and set the protection to "Rely on server..." and this saves all the connection info without the hassle of package passwords. We use it for both SQL Server and Oracle logins with OLEDB.
It also makes backing up your packages simpler as you only need to back up msdb.
You can divide up msdb into folders via Management Studio to group your packages too.
You can then run the package from sql agent without having to set anything else.
-
If you created the SSIS package under a Windows account then you can create a Proxy object to run the job under. The way I understand it a proxy treats a SQL SERVER and Windows login combination as an entity. You can assign this entity in the job step properties of the job under the General tab. Google this. Then you don't have to turn off encryption.
FYI - I used to have problems running these packages that encrypt sensitive info and my solution was to create the package directly on the machine it was to run on (with my windows login, or a login with full permissions if possible) and then do the above.
----------------------------------------------------
-
I do not know when this post was started but thanks to all!!!
You just saved me a whole lot of time.
Madame Artois
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply