June 26, 2014 at 7:28 am
I'm having difficulties storing and using a SQL Connection in an XML Package Configuration file. Here's what I did:
1. Start a new SSIS project called TestConfig
2. Added an OLEDB connection to a SQL server I can use. Call it Conn
3. Add an Execute SQL task, use connection Conn and the query 'Select 1'
4. Right-click the task and Execute the task -- it works (duh)
Now, add a config file:
5. Go to SSIS Package Configurations and create a new config file, call it TestConfig.dtsConfig
6. In the "Select Properties to Export", configure Check the Conn connection
7. Save the configuration
Now, try to use it:
8. Right-click the task and Execute the task (same one that worked above). It fails with the message:
[Execute SQL Task] Error: Failed to acquire connection "Conn". Connection may not be configured correctly or you may not have the right permissions on this connection.
Why is this? I've got a feeling that I'm missing something fundamental here.
June 26, 2014 at 11:05 am
OK -- I see why this is happening. When I created the config file, SSIS did not export the SQL login password, even though I told it to do that! I simply edited the config file by hand and added the password.
That was it!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply