June 28, 2013 at 10:26 am
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!
June 29, 2013 at 4:07 am
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[/url]
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
June 29, 2013 at 5:24 am
That's got me up and running!
many thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply