Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

saving password in SSIS package Expand / Collapse
Author
Message
Posted Sunday, September 9, 2007 10:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 31, 2012 2:37 PM
Points: 33, Visits: 68
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!
Post #397540
Posted Sunday, September 9, 2007 11:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 9:56 PM
Points: 2, Visits: 193
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.
Post #397596
Posted Monday, September 10, 2007 5:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 31, 2012 2:37 PM
Points: 33, Visits: 68
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!
Post #397908
Posted Wednesday, September 12, 2007 9:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:36 AM
Points: 965, Visits: 958

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

Post #398585
Posted Thursday, October 9, 2008 3:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:16 PM
Points: 6, Visits: 11
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



Post #583628
Posted Monday, November 8, 2010 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 7:21 PM
Points: 2, 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.
Post #1017281
Posted Monday, November 8, 2010 10:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 124, Visits: 488
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?!?!
Post #1017303
Posted Thursday, November 11, 2010 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1019152
Posted Thursday, November 11, 2010 2:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 28, 2014 5:47 AM
Points: 1,904, Visits: 2,827
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?!


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1019158
Posted Thursday, November 11, 2010 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #1019177
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse