SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


saving password in SSIS package


saving password in SSIS package

Author
Message
shank-130731
shank-130731
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 74
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!
WAOR
WAOR
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 209
Have you tried open that scheduled job and open connection tab, put your password in connection string (Password=<your sql server password>? 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
shank-130731
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 74
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
S. Kusen
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2921 Visits: 1120

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
ssqadir
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 12
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
Pakorn Pankasem
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 43
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
tmitchelar
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 508
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
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23602 Visits: 5314
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
Henrico Bekker
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7430 Visits: 5012
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
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23602 Visits: 5314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search