May 22, 2006 at 8:50 am
Hi all...
Unfortunately I'm limitted on my project to SQL Server 2000. 2005 is
not an option. That being the case, I have two questions...
Is there any way through an ActiveX Script task (or a Dynamic
Properties Task, or any other way) to dynamically modify the type of
connection employed by a Microsoft Data Link at run time? What I mean
by that is that in some cases, I want the Microsoft Data Link to
connect to an Access database, in some cases to Oracle, in some cases
to a text file and in other cases to a SQL Server database. Looking at
my options, for example, with the Dynamic Properties task and the
parameters on the Microsoft Data Link task, it seems that things like
the database server name, and the actual database on the server, can be
editted, but the connection type cannot. Is this same limitation also
in play when trying to edit the properties of the Microsoft Data Link
from within an ActiveX task?
Also, I have a second, related question. But first, briefly, a little
background...
Ultimately, I'm trying to extract data from a set of databases (that is
not set in stone... it will vary over time), and store that data in my
main database on the SQL server. What I'd really like to do is have a
configuration file that lists a bunch of databases (i.e. database type
- Oracle, SQL Server, Access, ect.) along with a query to run on each.
The query would be the correct query to extract the data I need from
each database. Ultimately, the data I extract from each database will
be transformed and saved into a table in my main SQL Server database.
My question is, what is the best way to accomplish this - to read in
the configuration file and to iterate through however many records it
contains, connect to each of the specified databases (regardless of the
database type, Oracle, SQL ect.), run the specified query against the
database to extract the data I need, and then to move on to the next
entry in the config file and repeat?
I suspect the right way is to use an Active X control that modifies the
properties of my connection object based, and the query in my transform
task, based on the data on each line of my config file, but I'm not
exactly sure. Any advice would be very much appreciated.
Thanks in advance for any help.....
May 23, 2006 at 8:50 am
after you create your connection object, start a loop
for each connection string in The_List
set the connection string to the connection object
open the connection object
do some work
close the connection object
next
May 23, 2006 at 11:38 am
Something like this ought to set you on the right path. In your Web.Config or App.Config set up the ConnectionStrings that you want to iterate thru. You can get the settings of the actual strings from http://www.connectionstrings.com/ (Very nice site!). Using ODBC gives you a lot of flexibility that you can't get using the native sql or oracle database drivers.
C# code
Imports System.Data.Odbc
OdbcConnection myconnection;
int ConnectionStrings = Convert.ToInt32(ConfigurationSettings.AppSettings("ConnectionStrings"));
if (ConnectionStrings > 0)
{
for (int J = 0; J < ConnectionStrings; J++) // for each connection string in the list
{
string CurrentConnectionString = ConfigurationSettings.AppSettings["ConnectionString" + J].ToString();
myconnection.ConnectionString = CurrentConnectionString;
myconnection.Open();
do some work
myconnection.Close();
}
}
Web.Config
<configuration>
<appSettings>
<add key="ConnectionStrings" value="4" />
<add key="ConnectionString0" value="sql server connection string" />
<add key="ConnectionString1" value="oracle connection string" />
<add key="ConnectionString2" value="access connection string" />
<add key="ConnectionString3" value="excel connection string" />
</appSettings>
</configuration>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply