saving password in SSIS package

Question

saving password in SSIS package

shank-130731

SSC Eights!

Points: 890
More actions
September 9, 2007 at 10:49 am

#118555

I'm having an issue with an SSIS package. It supposed to run on a schedule and pull data from one server (SQL1) to another (SQL2). Unfortunately, I'm using a shared server and I'm not that admin. So I'm limited to what I can try. According to the admin, my package is not retaining the password when I build it. It runs fine in debug mode on my machine, but will not run on his. I found this link...
http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm
... and tried it. It describes how to create an XML for validation. Still no good. He sent a screenshot indicating error code: DTS_E_CANNOTACQUIRECONNECTION and OLE DB Source failed validation. In effect, login failed for SQL1 and it could not pull data.
How do I get the package to retain login data?
thanks!

Viewing 12 posts - 1 through 11 (of 11 total)

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

waor SSC Journeyman Points: 82 More actions · Answer 1

Have you tried open that scheduled job and open connection tab, put your password in connection string (Password=<your sql server password&gt? Sometimes I found that even put the password here, you need to open the job and check at connection check box , and put your password again to make the job runs successfully. Hope this help.

shank-130731 SSC Eights! Points: 890 More actions · Answer 2

This is really getting to be a pain! I found this link in the SQL Server Help.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsprotectionlevel.aspx

If I read this correctly, there's no way I can create a package, upload it to an unknown admin to install on a shared server. It appears all encryption is based on the user. Well, I'm the user who creates the package and the admin, who is a different user with different permissions is installing it. Is there a way to build this package with no encryption? How else can I get this to work?

thanks!

S. Kusen SSChampion Points: 10936 More actions · Answer 3

shank,

Change the package security to "EcryptSensitiveWithPassword". Click on an open area of the ssis control flow tab and then go to the Properties window. There is a section for package security in that properties window. Set this password to something you can provide the admin.

When the admin creates the job to execute this package, have him use the File System option. He will then browse to the .dtsx file. After selecting the file, have him click on the "Command Line" tab of the SSIS job step. He will be prompted for the package password. Have him enter it, and that should allow the SSIS package to run as a job.

That's the relatively quick fix. I would recommend looking up "SQL Server Deployment" and utilizing the Deployment Utility" for SSIS. There is a lot of stuff to learn and figure out.

Let me know if that info helps or if you need more help.

Steve

ssqadir Valued Member Points: 58 More actions · Answer 4

Steve,

As per your suggestion I apply the changes and it worked.

My scenario was as follows:

Transferred the tables from Oracle into SQL Server 2005 and save the SSIS Package at the end.

However I modified the package and add more components as business requirement, it fails to save the password on runtime, as a result package ended with failure.

However when I apply the changes and changed default setting to "EncryptSensitiveWithPassword",

it works fine and package ended with success.

Thanks for your input.

Syed

Pakorn Pankasem SSC Journeyman Points: 78 More actions · Answer 5

Hi wanlapa linlawan

When you help someone please understand what they need!!! or what is problme!!!

You are head of DB and Bi so you should read more....

I interview you and i help you pass interview because you need money to take care your family. Currently, I manage DB2 V9 and SQL so i know the knowledge on DB2 very difference on SQL server.

May be you don't know buffer pool or performance tuning on DB2 .....

This issue should fix on SSIS on visual studio and looking to connection tab with input login and user name on OLDB

Not relate to SQL job or schedule job. Just fix the OLEDB connection in SSIS.

May be he can use wizard to generate SSIS and save package on file for run on job or command line that it helpful.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 6

Pakorn Pankasem (11/8/2010)
Hi wanlapa linlawan
When you help someone please understand what they need!!! or what is problme!!!
You are head of DB and Bi so you should read more....
I interview you and i help you pass interview because you need money to take care your family.

Huh?!?!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

tmitchelar (11/8/2010)
Pakorn Pankasem (11/8/2010)
Hi wanlapa linlawan
When you help someone please understand what they need!!! or what is problme!!!
You are head of DB and Bi so you should read more....
I interview you and i help you pass interview because you need money to take care your family.
Huh?!?!

I think we just witnessed a smackdown.. wow..

CEWII

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 8

Elliott Whitlow (11/11/2010)
tmitchelar (11/8/2010)
Pakorn Pankasem (11/8/2010)
Hi wanlapa linlawan
When you help someone please understand what they need!!! or what is problme!!!
You are head of DB and Bi so you should read more....
I interview you and i help you pass interview because you need money to take care your family.
Huh?!?!
I think we just witnessed a smackdown.. wow..
CEWII

......strange....did we miss something?!

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 9

Henrico Bekker (11/11/2010)
Elliott Whitlow (11/11/2010)
tmitchelar (11/8/2010)
Pakorn Pankasem (11/8/2010)
Hi wanlapa linlawan
When you help someone please understand what they need!!! or what is problme!!!
You are head of DB and Bi so you should read more....
I interview you and i help you pass interview because you need money to take care your family.
Huh?!?!
I think we just witnessed a smackdown.. wow..
CEWII
......strange....did we miss something?!

I can only guess these two know each other and the one hired the other.. not sure.. oh well..

CEWII

kcoons Valued Member Points: 65 More actions · Answer 10

Tried all the above. Read the following article: http://www.mssqltips.com/tip.asp?tip=1405. The system will not store the password for the Oracle database that the package needs to connect to. The command line will not store the password. I have tried everything that I can find. I tried manually fixing the command line within the Job, but it will not save the manually edited line. Any suggestions?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 11

First, I think you should start a new Post, this one is 8 months old.. In that post you should say what you tried and explicitly say what your package protection level is.

Thanks.

CEWII