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 table command

Hi,

I'm trying to run the ALTER TABLE command using a dynamic string for the
table, like so:

DECLARE @.TableName CHAR
SET @.TableName = 'Customers'
ALTER TABLE @.TableName
ADD ...blah

Is this possible? We know this works:

ALTER TABLE Customers ADD ...blah

It looks like I need a way to convert the CHAR value to a literal or perhaps
even a table ID?

Thanks in advance,
PaulYou would need to use dynamic query...

e.g.
exec('alter table '+@.tb+' add blah')

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"Paul Sampson" <psampson@.uecomm.com.au> wrote in message
news:1061964209.790366@.proxy.uecomm.net.au...
> Hi,
> I'm trying to run the ALTER TABLE command using a dynamic string for the
> table, like so:
> DECLARE @.TableName CHAR
> SET @.TableName = 'Customers'
> ALTER TABLE @.TableName
> ADD ...blah
> Is this possible? We know this works:
> ALTER TABLE Customers ADD ...blah
> It looks like I need a way to convert the CHAR value to a literal or
perhaps
> even a table ID?
> Thanks in advance,
> Paul|||"Paul Sampson" <psampson@.uecomm.com.au> wrote in message news:<1061964209.790366@.proxy.uecomm.net.au>...
> Hi,
> I'm trying to run the ALTER TABLE command using a dynamic string for the
> table, like so:
> DECLARE @.TableName CHAR
> SET @.TableName = 'Customers'
> ALTER TABLE @.TableName
> ADD ...blah
> Is this possible? We know this works:
> ALTER TABLE Customers ADD ...blah
> It looks like I need a way to convert the CHAR value to a literal or perhaps
> even a table ID?
> Thanks in advance,
> Paul

You can use dynamic SQL:

declare @.tablename sysname
set @.tablename = 'Customers'
exec('alter table dbo.' + @.tablename + ' add ...')

See here for more information on dynamic SQL:

http://www.algonet.se/~sommar/dynamic_sql.html

By the way, if you declare a variable as CHAR without a length, it
will default to CHAR(1). For object names, sysname is a better choice.

Simon|||Thanks Simon - a common suggestion and one that I'll be sure to remember.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:60cd0137.0308270337.2bbb79e4@.posting.google.c om...
> "Paul Sampson" <psampson@.uecomm.com.au> wrote in message
news:<1061964209.790366@.proxy.uecomm.net.au>...
> > Hi,
> > I'm trying to run the ALTER TABLE command using a dynamic string for the
> > table, like so:
> > DECLARE @.TableName CHAR
> > SET @.TableName = 'Customers'
> > ALTER TABLE @.TableName
> > ADD ...blah
> > Is this possible? We know this works:
> > ALTER TABLE Customers ADD ...blah
> > It looks like I need a way to convert the CHAR value to a literal or
perhaps
> > even a table ID?
> > Thanks in advance,
> > Paul
> You can use dynamic SQL:
> declare @.tablename sysname
> set @.tablename = 'Customers'
> exec('alter table dbo.' + @.tablename + ' add ...')
> See here for more information on dynamic SQL:
> http://www.algonet.se/~sommar/dynamic_sql.html
> By the way, if you declare a variable as CHAR without a length, it
> will default to CHAR(1). For object names, sysname is a better choice.
> Simon

Alter a table all in C#

Is there a way to add a column to an existing table and do it all in C#

If my query string is as follows how do I execute the query?
ALTER TABLE interests ADD COLUMN Swim VARCHAR(1) NOT NULL DEFAULT('n')

Thanks
MoonWaYou could useExecuteNonQuery on a SqlCommand object.|||Thanks I got it working.

MoonWa