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

how to associate password with package? Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 7:50 AM
Points: 8, Visits: 91
Apologies if this has been asked a zillion times before - I looked but couldn't find an answer.

I've started using SSIS to import some files into database on my local PC sql server instance; I then copy that out to other servers as needed. It's all working OK so I thought I'd amend the packages and import straight into one of the development servers. I changed the connection managers to point to that server, tested them in the SSIS frontend, all seemed OK, but when I execute the package it fails "to acquire connection ... Connection may not be configured correctly or you may not have the right permissions on this connection". I think the difference is that when I execute the package on my local server it uses windows authentication - but on the dev server I need to provide user ID and password. I tested that theory by creating a new login using SQL rather than windows authentication - so I can now reproduce the problem when executing the package on my local instance.

I've found some articles about using package configuration to store a password but haven't been able to get this to work. I don't like the sound of storing a password in a plain text file but I'll worry about that after I get the dratted process working.

Assistance please!
Post #1468618
Posted Saturday, June 29, 2013 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
You can skip straight to the section abot 1/3rd of the way down titled Setting Up Your XML Configuration File: XML Configuration files in SQL Server Integration Services

For your situation, unlike in the article where Robert selects the alue property of several variables, you will want to find the section for Connection Managers and select the ConnectionString property of your chosen Connection Manager. When you complete the Package Configuration wizard go look at the resulting .dtsConfig file in notepad and it will be easy to tell what you need to change when you deploy the Package and config file to a new server.

One wrinkle in using XML Package Configurations to be aware of, the path to the .dtsConfig file is stored in the .dtsx file when you set it up the way Robert has meaning that the path you choose to store the file must exist on all servers where you plan to deploy it. Sometimes this is difficult to predict. An alternative is to use an Indirect Configuration where the path to the .dtsConfig file is stored in an Environment Variable, but that has it's own pluses and minuses. Post back with specific questions if you have doubts or want to discuss further.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1468783
Posted Saturday, June 29, 2013 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 7:50 AM
Points: 8, Visits: 91
That's got me up and running!

many thanks
Post #1468789
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse