SSIS Connection not updated programmatically

#21

Try this:

Use an Execute SQL Task to read the table and load results into a variable
of type: Object. Then have a For Each loop iterate over the records in the
(Object) table, assigning fields in the table to Variables in the package.
Have a single Execute SSIS Package inside the loop. Use its Expressions to
dynamically decide which package to run based on the contents of the
respective variables.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

I have a Master Package with script task which is used to get the list of
packages and parameters from database table and then run the required
packages (the number of packages we need to run varies).

And Master Package is run using a SQL Agent job.

"Todd C" wrote:

SSIS tends to be a little protective of saved passwords.

Why use a script task when you have a built-in task for running a child
package? Just curious.

You may need to pass the ConnectionString (with password) to the child via a
String variable, then set the ConnectionString in the child using an
Expression.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

I'm trying to modify an existing connection for a package which I call from
another package using a script task. The connection string gets modified but
password does not get modified.

Here is the code I'm using -

Dim pkg As Package() = app.LoadPackage(MyPackageFullPath, Nothing)
Dim ConMgr As ConnectionManager
ConMgr = pkg.Connections("MyConnectionString")
ConMgr.ConnectionString = "Data Source=MyServer;User
ID=MyID;Password=MyPassword;Initial
Catalog=MyDatabase;Provider=SQLOLEDB.1;Persist Security Info=True;"

Server, database, User ID information gets modified but password never gets
updated and results in failure.

Any help is appreciated.
Thanks

#22

Try this:

Use an Execute SQL Task to read the table and load results into a variable
of type: Object. Then have a For Each loop iterate over the records in the
(Object) table, assigning fields in the table to Variables in the package.
Have a single Execute SSIS Package inside the loop. Use its Expressions to
dynamically decide which package to run based on the contents of the
respective variables.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

I have a Master Package with script task which is used to get the list of
packages and parameters from database table and then run the required
packages (the number of packages we need to run varies).

And Master Package is run using a SQL Agent job.

"Todd C" wrote:

SSIS tends to be a little protective of saved passwords.

Why use a script task when you have a built-in task for running a child
package? Just curious.

You may need to pass the ConnectionString (with password) to the child via a
String variable, then set the ConnectionString in the child using an
Expression.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

I'm trying to modify an existing connection for a package which I call from
another package using a script task. The connection string gets modified but
password does not get modified.

Here is the code I'm using -

Dim pkg As Package() = app.LoadPackage(MyPackageFullPath, Nothing)
Dim ConMgr As ConnectionManager
ConMgr = pkg.Connections("MyConnectionString")
ConMgr.ConnectionString = "Data Source=MyServer;User
ID=MyID;Password=MyPassword;Initial
Catalog=MyDatabase;Provider=SQLOLEDB.1;Persist Security Info=True;"

Server, database, User ID information gets modified but password never gets
updated and results in failure.

Any help is appreciated.
Thanks

#23

Try this:

Use an Execute SQL Task to read the table and load results into a variable
of type: Object. Then have a For Each loop iterate over the records in the
(Object) table, assigning fields in the table to Variables in the package.
Have a single Execute SSIS Package inside the loop. Use its Expressions to
dynamically decide which package to run based on the contents of the
respective variables.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

I have a Master Package with script task which is used to get the list of
packages and parameters from database table and then run the required
packages (the number of packages we need to run varies).

And Master Package is run using a SQL Agent job.

"Todd C" wrote:

SSIS tends to be a little protective of saved passwords.

Why use a script task when you have a built-in task for running a child
package? Just curious.

You may need to pass the ConnectionString (with password) to the child via a
String variable, then set the ConnectionString in the child using an
Expression.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

I'm trying to modify an existing connection for a package which I call from
another package using a script task. The connection string gets modified but
password does not get modified.

Here is the code I'm using -

Dim pkg As Package() = app.LoadPackage(MyPackageFullPath, Nothing)
Dim ConMgr As ConnectionManager
ConMgr = pkg.Connections("MyConnectionString")
ConMgr.ConnectionString = "Data Source=MyServer;User
ID=MyID;Password=MyPassword;Initial
Catalog=MyDatabase;Provider=SQLOLEDB.1;Persist Security Info=True;"

Server, database, User ID information gets modified but password never gets
updated and results in failure.

Any help is appreciated.
Thanks

#24

Try this:

Use an Execute SQL Task to read the table and load results into a variable
of type: Object. Then have a For Each loop iterate over the records in the
(Object) table, assigning fields in the table to Variables in the package.
Have a single Execute SSIS Package inside the loop. Use its Expressions to
dynamically decide which package to run based on the contents of the
respective variables.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

I have a Master Package with script task which is used to get the list of
packages and parameters from database table and then run the required
packages (the number of packages we need to run varies).

And Master Package is run using a SQL Agent job.

"Todd C" wrote:

SSIS tends to be a little protective of saved passwords.

Why use a script task when you have a built-in task for running a child
package? Just curious.

You may need to pass the ConnectionString (with password) to the child via a
String variable, then set the ConnectionString in the child using an
Expression.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

I'm trying to modify an existing connection for a package which I call from
another package using a script task. The connection string gets modified but
password does not get modified.

Here is the code I'm using -

Dim pkg As Package() = app.LoadPackage(MyPackageFullPath, Nothing)
Dim ConMgr As ConnectionManager
ConMgr = pkg.Connections("MyConnectionString")
ConMgr.ConnectionString = "Data Source=MyServer;User
ID=MyID;Password=MyPassword;Initial
Catalog=MyDatabase;Provider=SQLOLEDB.1;Persist Security Info=True;"

Server, database, User ID information gets modified but password never gets
updated and results in failure.

Any help is appreciated.
Thanks

#25

Try this:

Use an Execute SQL Task to read the table and load results into a variable
of type: Object. Then have a For Each loop iterate over the records in the
(Object) table, assigning fields in the table to Variables in the package.
Have a single Execute SSIS Package inside the loop. Use its Expressions to
dynamically decide which package to run based on the contents of the
respective variables.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

Quote:

I have a Master Package with script task which is used to get the list of
packages and parameters from database table and then run the required
packages (the number of packages we need to run varies).

And Master Package is run using a SQL Agent job.

"Todd C" wrote:

SSIS tends to be a little protective of saved passwords.

Why use a script task when you have a built-in task for running a child
package? Just curious.

You may need to pass the ConnectionString (with password) to the child via a
String variable, then set the ConnectionString in the child using an
Expression.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

"xyz_jat" wrote:

I'm trying to modify an existing connection for a package which I call from
another package using a script task. The connection string gets modified but
password does not get modified.

Here is the code I'm using -

Dim pkg As Package() = app.LoadPackage(MyPackageFullPath, Nothing)
Dim ConMgr As ConnectionManager
ConMgr = pkg.Connections("MyConnectionString")
ConMgr.ConnectionString = "Data Source=MyServer;User
ID=MyID;Password=MyPassword;Initial
Catalog=MyDatabase;Provider=SQLOLEDB.1;Persist Security Info=True;"

Server, database, User ID information gets modified but password never gets
updated and results in failure.

Any help is appreciated.
Thanks

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SSIS Connection not updated programmatically

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

RE: SSIS Connection not updated programmatically - 12-09-2008 , 11:14 AM

RE: SSIS Connection not updated programmatically - 12-09-2008 , 11:14 AM

RE: SSIS Connection not updated programmatically - 12-09-2008 , 11:14 AM

RE: SSIS Connection not updated programmatically - 12-09-2008 , 11:14 AM

RE: SSIS Connection not updated programmatically - 12-09-2008 , 11:14 AM