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

Connect to SQL Server 2005 Problem

Hi all,

I have created an Outlook Add-in using VS2005, which basically populates a custom WinForm with some data retrived from the backend SQL Server 2005 database.

The SQL Server is installed on the same computer as the development environment (VS 2005).

The add-in code compiles OK and I have created a setup package for this app, which installs the required files with no problem. The package has been tested working on the development machine. But as soon as I install it on another computer (installation was no problem, and outlook launched the add-in as expected) and click on the add-in button I made on the menu bar, the add-in hangs up.

I have checked the tables on the sql server and found that no operations was performed whatsoever. Suspecting there are errors connecting to the server, I also threw in some Try - Catch pairs to catch the exceptions, but still nothing pops up as if the app couldn't even start while it runs just fine on the development machine.

Below is how I connect to the SQL Server 2005 in my code (VB)

Dim cn As ADODB.Connection = New Connection
Dim rst As Recordset, query As String
cn.ConnectionString = "Provider=SQLNCLI;Server=Development;Database=Project1;Trusted_Connection=yes;"
cn.Open()

query = "SELECT * FROM Sample_Tab;"
''' Do something with the connection
Try
rst = cn.Execute(query)
Catch ex As Exception
MsgBox("ADODB Error: " & ex.ToString)
Exit Sub
End Try

cn.Close()

Any help will be greatly appreciated.

-rahx

Did you enable NP and/or TCP on your SQL Server? You need make sure that your sql can accept remote connection. Do you have firewall? Please follow the instruction and give us me info http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

|||Problem solved - my mistake. I used sql native client in my code but forgot to include a sql native client msi in the installation package.

After installing the native client on the test computer, the connection was established successfully.