Hi,
I am trying to connect to multiple Interbase databases
via the DTS package. I have successfully installed the
Interbase driver and ODBC connects successfully to all.
The package runs successfully the first time, connecting
to InterbaseDB A, but fails to change connection to the
next InterbaseDB B.
I am using global variables to keep count of databases to
connect etc. The global variable gets updated
successfully, but the data pump is still going to the old
db A.
Is there a way that I can control the dynamic connection
to databases via a script, before the datapump
executes ?
I am using the loop code listed on Alan's website:
http://www.sqldts.com/default.aspx?6,103,246,0,1
Thanks in Advance,
Parul

Task1: Execute SQL Task to get total Db's to connect to.
Task2: ActiveX script that checks for package to excecute
when count > 0
Task3: Dynamic Properties Task that sets Datasource of
InterbaseODBC driver Connection to new DB
Task4: Populate BranchID global variable with new db id.
Task5: Dynamic Prop. Task to set From_Date Global
Variable.
Task6: Dynamic Prop. Task to set To_Date Global Variable
(selected from interbase branch db...don't if this
works).
Task7: 4 datapumps to select data via ActiveX script in
Data Pump properties. Source Connection--Interbase ODBC
Driver conn, Destination Conn--SQL Server2000.
Task8: Update DB to OK Flag to say selects took place,
table updated in SQL server.
Task9: Loop ActiveX script

Thanks !

What is it that you are using to change the connection ?
You may need to make the change in two places.

1. The Connection
2. The SourceObjectName

I do not work with Interbase so do not know what it puts in as the
SourceObjectName. I may be qualified by the DB as well.



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Parul" <bhandariparul (AT) hotmail (DOT) com> wrote

Thanks for writing back.
Currently I am attempting to change the connection via a
DTS Dynamic Task.
In the properties of the dynamic task, I am running a SQL
query, that selects the new DB id (DSN Name stored in SQL
server table), and populates the Datasource of the
interbase driver connection.

How do I change the connection as suggested by you ? i.e.
1. The Connection
2. The SourceObjectName

How can I control which connection the datapump should
use? Can I have a coonection string with dynamic
server/db name fields in an active X script before the
datapump executes ?
Thanks again. Please see DBforums for attached .doc with
screen dumps.
-Parul

Hi Alan,

Thanks for writing back.

Currently I am attempting to change the connection via a DTS
Dynamic Task.

In the properties of the dynamic task, I am running a SQL query, that
selects the new DB id (DSN Name stored in SQL server table), and
populates the Datasource of the interbase driver connection.



How do I change the connection as suggested by you ? i.e.

1. The Connection

2. The SourceObjectName



How can I control which connection the datapump should use? Can I have a
coonection string with dynamic server/db name fields in an active X
script before the datapump executes ?

Thanks again.

-Parul





Originally posted by Allan Mitchell


--
Posted via http://dbforums.com

Hi,

This for Darren, who wrote to me on the other thread from Joel today.

I have changed the SourceObjectName as suggested by Alan. But Not sure
what the syntax is.



And please let me know how to use the Close Connection On Completion
option. As in my DTS package, its the the datapumps that still look at
the old connection.



Thanks for your help.

-Parul


--
Posted via http://dbforums.com

IT WORKED!!

I changed the Close Connection after Completion option property of the
data pump and now its looping properly into the next database !

Thanks Darren and Allan.

Cheers!

Parul


--
Posted via http://dbforums.com