Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-17
10
Medium Priority
?
820 Views
Last Modified: 2016-02-11
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
Comment
Question by:Grffster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 11

Expert Comment

by:SThaya
ID: 39253104
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
 

Author Comment

by:Grffster
ID: 39253139
It will have to be hard-coded in to the package then though won't it?
0
 
LVL 11

Expert Comment

by:SThaya
ID: 39253151
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 11

Expert Comment

by:SThaya
ID: 39253156
or try Encrypt all with password  option in package protection level
0
 

Author Comment

by:Grffster
ID: 39253185
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
 

Author Comment

by:Grffster
ID: 39253192
Same result with the Encrypt All With Password option.
0
 
LVL 11

Expert Comment

by:SThaya
ID: 39253206
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
 

Author Comment

by:Grffster
ID: 39253216
Same  username and password. This is the strange issue with it. I can understand if they were different: "Login failed for user 'XXX'.".
0
 
LVL 11

Accepted Solution

by:
SThaya earned 2000 total points
ID: 39253265
0
 

Author Comment

by:Grffster
ID: 39253323
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question