Altering a connection manager dynamically via a variable

Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).

To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.

When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.

Any ideas why this isn’t working?

You shouldn't use script tasks to try and change connection manager connection strings. Use this technique: http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

-Jamie

|||Use the mthods in Jamie link that is how I do mine and it works great.

Alter a table by using a variable as column name

Hi All,

i would like to alter a table by using a @.Variable as Column name like this:

Code Snippet

DECLARE @.VARIABLE AS nvarchar(50)

SET @.VARIABLE = 'Column1'

ALTER TABLE #TempTable ADD

@.VARIABLE AS nvarchar(50)

But it looks like, that i cant alter a table by using a variable as column name?

Can someone help me out?

Greetings,

Andreas

hi,
you can use dynamic query e.g.

Code Snippet

DECLARE @.VARIABLE AS nvarchar(50)

SET @.VARIABLE = 'Column1'

declare @.str varchar(4000)
set @.str = 'ALTER TABLE #TempTable ADD ' + @.variable + ' nvarchar(50)'
exec(@.str)

- clintz|||

Well ... that was a fast help!

Big thanks to you, clintz.

I think, i should put some time into dynamic query