Connecting to multiple SQLserver

Hello,

I am working on to design SSIS package which need to run scripts against multiple sqlserver and need help.

Basically I need to connect several SQLserver machine to get a data and insert to our central respository.

Any help or tip appreciated.

Thanks

--

Farhan

Farhan H Soomro wrote:

Hello,

I am working on to design SSIS package which need to run scripts against multiple sqlserver and need help.

Basically I need to connect several SQLserver machine to get a data and insert to our central respository.

Any help or tip appreciated.

Thanks

--

Farhan

Use multiple connection managers.

-Jamie

|||Is there any way I can read from text file salserver name and connect to particular server and run the scripts?|||

Farhan H Soomro wrote:

Is there any way I can read from text file salserver name and connect to particular server and run the scripts?

Yes. SSIS Configurations are provided to help you do exactly this.

Creating Package Configurations
(http://msdn2.microsoft.com/en-us/library/ms141132.aspx)

-Jamie

|||

If you have a list of servers that you want to run some SQL against, you can create two packages.

The simplest way to do this is create a SQL Server configuration table.
In the first package create a ForEach loop that processes each server in a resultset (I'd stick the list of servers in a SQL table so you can use it in reports).
In the ForEach loop
execute a SQL task to update the connection for the second package (the source server connection).
Then execute the second Package

In the Second package just create two connections, the source server to run the scripts against and the destination server to put the results in. Just use a data flow task to move the data.

|||

Thanks for the help. Just wondering how to setup SQL task to update the connection for the second package ?

TIA

--

Farhan

Connected to which database

Hello all,

I have a scripts which needs the information about the database in
which it is running.

How can I find out this information.

For example if I want to know the User who has satrted this report, I
use the SYSTEM_USER procedure.

Does an equal procedure exist to find out, on which database the user
is connected to.

Regrads
Franz-JosefYou can the name of the current database with DB_NAME():

SELECT DB_NAME()

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Franz-Josef" <Post@.tophoven.net> wrote in message
news:a0fd39b2.0404280302.403618ee@.posting.google.c om...
> Hello all,
> I have a scripts which needs the information about the database in
> which it is running.
> How can I find out this information.
> For example if I want to know the User who has satrted this report, I
> use the SYSTEM_USER procedure.
> Does an equal procedure exist to find out, on which database the user
> is connected to.
> Regrads
> Franz-Josef

connect to SQL server 2005 databse using perl

we moved recently to SQL server 2005, i have perl scripts that i use to connect to the database, retrieve information and updata datas. since we moved the perl scripts are unable to connect to the database. I created the system DSN in the odbc manager that is used to connect to that database. this is the error message we are getting when we try to cnnect to the databse :
DBI connect('billing3','dbo',...) failed: [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver specified (SQL-IM002)(DBD: db_login/
SQLConnect err=-1) at night_process.pl line 30

the perl script that tries to connect is :
#!/usr/bin/perl -w
use File::Copy;
use Date::Calc qw(Today Add_Delta_Days);
use Cwd;
use Net::FTP;
use DBI; # database connection ;

$DSN="billing3";

$user="dbo";
$pass="toto";

#connect to the database

$dbh = DBI->connect("DBI:ODBC:$DSN", $user, $pass);This is an SSIS forum. Your post has nothing in regards to SSIS so I would advise posting to another forum or several others as it is unclear which forum would be best suited to answer this question.

Thanks,
Matt