Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 880
  • Last Modified:

SSIS Package losing password in dynamic ole db connection on second run through a ForEach Loop container

I've built an ssis package to loop through a list of servers. I'm using a foreach loop container to do the looping with a sql server task querying a dynamic ole db connection against the server for that loop using SQL Server Authentication. It seems to work fine for the first iteration but then loses the password for the second iteration. Does anyone know how to fix this please? I've tried setting the protectionlevel property for the package to be EncryptSensitiveWithPassword as some sites suggest but I get the same problem.
0
Grffster
Asked:
Grffster
  • 5
  • 5
1 Solution
 
SThayaTechnical MAnagerCommented:
Hi,

  use the below expression in the connection string properties

"Data Source="+ REPLACE ( @[User::Desti_server]  ,"\\","\\")+";User ID="+ @[User::Desti_UName] +";Password="+ @[User::Desti_Pwd] +";Initial Catalog="+ @[User::Desti_DB] +";Provider=SQLNCLI10.1;Persist Security Info=True;Auto Translate=False;"
 
this will pass your password dynamically
0
 
GrffsterAuthor Commented:
It will have to be hard-coded in to the package then though won't it?
0
 
SThayaTechnical MAnagerCommented:
can you please tell me where are you storing the connection string properties like server,db,password etc

and

can you give me the stepts you done with your package
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SThayaTechnical MAnagerCommented:
or try Encrypt all with password  option in package protection level
0
 
GrffsterAuthor Commented:
The purpose of the package is to loop through a number of servers checking for SQL Agent job failures. I have a table in a database on my warehouse server that contains a list of servers to check. I run a sql task to populate a variable of type object with the result set containing these server names and then use a foreach loop container to loop through these connecting to the SQL server using a dynamic ole db connection in connection manager.  The server name is set by a variable (equating to that loop of the container) populating the ServerName Expression. This connection is using a SQL Server Authentication user account and password. I think it's this password that's being dropped each time.
0
 
GrffsterAuthor Commented:
Same result with the Encrypt All With Password option.
0
 
SThayaTechnical MAnagerCommented:
so each server has different user name and password or same ?

if its different you have to pass username,server name and password dynamically ......
0
 
GrffsterAuthor Commented:
Same  username and password. This is the strange issue with it. I can understand if they were different: "Login failed for user 'XXX'.".
0
 
SThayaTechnical MAnagerCommented:
0
 
GrffsterAuthor Commented:
Ah! Bingo!!! Just realised the problem thanks to you asking the question about the DB. The dynamic ole db connection was trying to connect to a database that only existed on the first server. I needed to pick a common database to all servers e.g. tempdb. Doh!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now