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.