Re: Changing connection information on OLEDB connection

I did that and the variable is setting ok-it does change with each
loop. You can set the password property of an OLEDB connection with an
expression it seems--only ServerName, UserName and InitialCatalog. I
will try setting those dynamically (keeping password static) and see
what happens then. I will keep you posted.

Kayda

You can set all connection properties

On Feb 3, 4:18 am, "Allan Mitchell" <a...@xxxxxxxxxxxxxxxxxx> wrote:
OK so this is SSIS

On the face of it this looks OK.

What I would do is set a breakpoint on the "On Each Iteration Of The
Loop" event.

I would then set a watch on the variable to make sure that the
connection string is being set properly.

What I might do is have a variable called cnString that is
"EvaluateAsExpression" = true. I would build the string in there and
simply watch this variable.

Also. Can you not set the ServerName, UserName and Password properties
individually?

--

Allan Mitchellhttp://wiki.sqlis.com|http://www.sqlis.com|http://www.sqldts.com|http://www.konesans.com

"Kayda" <blair...@xxxxxxxxx> wrote in message

news:1170385365.762151.167240@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

I am trying to set the connection string in a connection manager at
runtime. Here is what I have done:

1. Created a gv_DataSource, gv_Username and gv_Password

2. Created a ForEach Loop that reads DataSource, Username and password
values from a variable (it is an For Each ADO loop Enumerator). The
ADO recordset is read into by an Execute SQL task before the loop.

3. Mapped values from the recordset to variable in the ForEach loop's
"Variable Mappings" page.

4. Used the variables in my Sybase OLEDB Connection Manager's
"Expression" property, setting the "ConnectionString" property to:

"Data Source=" + @[User::gv_DataSource] + ";User ID=" +
@[User::gv_Username] + " ;Password=" +
@[User::gv_Password] + ";Initial
Catalog=b=MydatabaseName;Provider=Sybase.ASEOLEDBProvider.2;Persist
Security Info=True;"

5. I set the values in my database table for the connection-I set 2
connections for which I have Sybase OLEDB datasources setup.

When I run the package, I just get the first server's data twice, it
doesn't set the second server's data during the second loop. I made
sure the first one was working (i.e. the ConnectionString's property
was being set by the data from the current variables) by setting the
variables incorrectly in the variable properties page, and then
running the package. So the first row of connection information is
working, but the second loop around it doesn't seem to be working. I
used a msgbox in a script task to show that the variables are mapping
correctly in the loop, so it seems the second time around the
connection information isn't taking from the variables.

What am I doing wrong here?

Thanks,

Kayda


.

Relevant Pages

  • Re: Changing connection information on OLEDB connection
    ... If you are then look at the properties of the Parent data flow task and you'll see the SqlCommand property of the Data Reader source adapter exposed there. ... Sybase OLEDB datasources, it seems to cache one connection so, even ... >> Loop" event. ... but the second loop around it doesn't seem to be working. ...
    (microsoft.public.sqlserver.dts)
  • Re: TCP listener
    ... This loop is required because of the main purpose of the program I am trying ... I am using the push server product's .Net sample and trying to modify it ... that it only works untill I close connection to the c# listener. ... private volatile bool monitorPort = true; ...
    (microsoft.public.dotnet.languages.csharp)
  • SSIS - Error Messages when looping through Excel Files
    ... What's the trick to use a Foreach File in Folder loop when looping through ... Now if I have my XL Connection manager set to a specific file, ...
    (microsoft.public.sqlserver.dts)
  • Re: Changing connection information on OLEDB connection
    ... Sybase OLEDB datasources, it seems to cache one connection so, even ... Loop" event. ... but the second loop around it doesn't seem to be working. ...
    (microsoft.public.sqlserver.dts)
  • Re: Changing connection information on OLEDB connection
    ... What I would do is set a breakpoint on the "On Each Iteration Of The Loop" event. ... I would then set a watch on the variable to make sure that the connection string is being set properly. ... When I run the package, I just get the first server's data twice, it ... but the second loop around it doesn't seem to be working. ...
    (microsoft.public.sqlserver.dts)
Loading