Connect to many server

Hello,

I asked this question before and being told to use configuration table to connect multiple server. Here is the question again

Problem: I need to connect around 50 different server and run the script (which creates a temp table and returned the result set which I need to insert or update to table)

I try to create configuration table added my servers there by when I am runing the package it is only running against one server only where is connection is created.

Here is SSIS Configuration table

ServerName, sqlserver1, \Package.Connectionsnstm0621dap.MSSQLCentral].Properties[ServerName], String

ServerName, sqlserver2, \Package.Connectionsnstm0621dap.MSSQLCentral].Properties[ServerName], String

ServerName, sqlserver3, \Package.Connectionsnstm0621dap.MSSQLCentral].Properties[ServerName], String

the package is only running againt the nstm0621dap server and not taking servername from config table.

Any help is appreciated.

Thanks

--

Farhan

Hi Farhan

Im doing almost similar thing.. well only with one server right now (im getting better with ssis as time goes by), but extract data from a lot of different tables with same schema.

I'll soon be facing the same scenario as you, my knowledge is very limited right now regarding the configuration table. I guess you're trying to connect to one server at a time and run the scripts you described.

Could the reason be that the SSIS Configuration table don't support multivalues?
The way I would do it would be to create a table on my mainserver, read it and use a foreach container and iterate through the different servers...

Maybe I don't understand your question

|||Well, the configurations just modify the appropriate property when the configurations are loaded. If the property is single-value (one server name), you can't insert multiple values into it - the last one wins.

To do what you want you need to use a loop, and modify the connection property for each iteration. See Books Online for more details of the ForEach loops.

Connect at only One port

When connecting to sql 2000 Enterprise manager opens multiple ports.
For example
Proto Local Address Foreign Address State
TCP 192.168.7.12:1433 192.168.7.12:3275 ESTABLISHED
TCP 192.168.7.12:1433 192.168.7.12:3634 ESTABLISHED
TCP 192.168.7.12:3634 192.168.7.12:1433 ESTABLISHED
How can I force sql to connect only at 1433 or other single port. I facts
is there such posibility?
Hi -
If you analyze the entries in the SQL Server error log file, you can verify
that the instance of SQL Server is listening on the correct IP address and on
the correct port. By default, a default instance of SQL Server listens on the
port 1433. You can also use Server Network Utility to verify the protocol
settings for SQL Server and to change the properties in SQL Server, including
the protocols that can connect to SQL Server and the ports that can be used.
For more information about using Server Network Utility, see the "SQL Server
Network Utility" topic in SQL Server Books Online.
Sometimes, SQL Server 2000 may not bind to port 1433 or any other specified
port. This problem may occur if the port is being used by another application
or if you are trying to connect by using an IP address that is not correct.
Therefore, the TCP/IP connections to SQL Server may not be successful and you
may receive the following error message in the SQL Server error log file:
2001-11-14 15:49:14.12 server SuperSocket Info: Bind failed on TCP port 1433.
Please check the below link :
http://support.microsoft.com/default.aspx?kbid=307197
Let me know if it helps you.
"Radin" wrote:

> When connecting to sql 2000 Enterprise manager opens multiple ports.
> For example
> Proto Local Address Foreign Address State
> TCP 192.168.7.12:1433 192.168.7.12:3275 ESTABLISHED
> TCP 192.168.7.12:1433 192.168.7.12:3634 ESTABLISHED
> TCP 192.168.7.12:3634 192.168.7.12:1433 ESTABLISHED
> How can I force sql to connect only at 1433 or other single port. I facts
> is there such posibility?
>

Connect at only One port

When connecting to sql 2000 Enterprise manager opens multiple ports.
For example
Proto Local Address Foreign Address State
TCP 192.168.7.12:1433 192.168.7.12:3275 ESTABLISHED
TCP 192.168.7.12:1433 192.168.7.12:3634 ESTABLISHED
TCP 192.168.7.12:3634 192.168.7.12:1433 ESTABLISHED
How can I force sql to connect only at 1433 or other single port. I facts
is there such posibility?Hi -
If you analyze the entries in the SQL Server error log file, you can verify
that the instance of SQL Server is listening on the correct IP address and o
n
the correct port. By default, a default instance of SQL Server listens on th
e
port 1433. You can also use Server Network Utility to verify the protocol
settings for SQL Server and to change the properties in SQL Server, includin
g
the protocols that can connect to SQL Server and the ports that can be used.
For more information about using Server Network Utility, see the "SQL Server
Network Utility" topic in SQL Server Books Online.
Sometimes, SQL Server 2000 may not bind to port 1433 or any other specified
port. This problem may occur if the port is being used by another applicatio
n
or if you are trying to connect by using an IP address that is not correct.
Therefore, the TCP/IP connections to SQL Server may not be successful and yo
u
may receive the following error message in the SQL Server error log file:
2001-11-14 15:49:14.12 server SuperSocket Info: Bind failed on TCP port 1433
.
Please check the below link :
http://support.microsoft.com/default.aspx?kbid=307197
Let me know if it helps you.
"Radin" wrote:

> When connecting to sql 2000 Enterprise manager opens multiple ports.
> For example
> Proto Local Address Foreign Address State
> TCP 192.168.7.12:1433 192.168.7.12:3275 ESTABLISHED
> TCP 192.168.7.12:1433 192.168.7.12:3634 ESTABLISHED
> TCP 192.168.7.12:3634 192.168.7.12:1433 ESTABLISHED
> How can I force sql to connect only at 1433 or other single port. I facts
> is there such posibility?
>

Conlidate databases for multiple installations

1. install a SQL Express instance such as myexpress;

2. create some databases with tables such as db1 and db2;

3. Reinstall SQL Express with the same instance name "myexpress". I assume we need reinstall SQL Express myexpress again becuase of bad operations.

4. Open the myexpress by management tool but I can not see db1 and db2 databases but the datafiles and logfiles are in the data directory.

The question is what I need to do so I can consolidate exited databases so they can be seen in "myexpress" instance by management tool?

Hmmm it seems it has nothing to do with Team System, I would recommend you to ask this in the SQL Server forums, in which you will get more help for sure, sorry and good luck.

I also think the step you are not doing is an attach of the databases.

confused about pull

I have setup multiple merge replications that are continuously updating.
Now, we need to have laptops in the field that connect occasionally that
will merge. Because of that, I assume it needs merge PULL subscription.
I have had tons of problems and it has never worked. Right now it seems
to always be a permission issue.
First, I need to test this in the office to make sure it works. If it is
working, can I take that laptop out in the field, connect via the
internet, and it will work there with no changes? This is needed because
90% of the time the user is in the field, but occasionally he is in the
office, and I don't want to have to change configurations when he comes
and goes.
Next, to get a pull setup and functioning in the office, this seems to
require both access to SQL (via a sql login I created) AND access to the
server via a windows login - is this true? If so, what is the windows
login access for (the snapshot I beleive)?
So, I guess in a perfect world the laptop can be first configured and
replicated in the office (to get the snapshot) and then taken out of the
office and connect via the internet.
Does all of this sound reasonable. I know (according to Hilary) this is
a very complex issue and I am discovering it is. Hopefully this is like
a push/merge that seemed complex until it was working, then it was(is)
easy.
Let me know people's thoughts.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
the initial Initialization process will require access to the share holding
the snapshot files. This access is by default achieved using the sql server
agent and impersonation. If the account which the agent uses doesn't have
rights to the distributor's working folder (snapshot share) or if the
account is simply not recognised on this server, then there will be
problems. In the case of a non-trusted environment you then have to use an
alternative (usually FTP) to get the initialization going. Once it's going,
the merge agent uses whatever you've set up to do synchronization, over your
configured port. This can be trusted or sql authentication, and by default
it is also trusted.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

configuring Active/Active...

You would need to install multiple instances of SQL Server
to do what you want. So Ins1 would own DB1, and Ins2 DB2
and DB3. (Or Two instances ... whatever you want). The
way failover clustering works is that upon a failure, the
whole SQL instance will fail over to another node, so if
you had a failure on Ins1, it would go to Node 2 (or 3 or
whatever) automatically.
So I'm not sure what it is you're asking since you get
that functionality.
Your apps would connect to the SQL virutal server/instance
that has the DBs. On a cluster, a SQL virtual server is
an instance.
Heelo AA,
Here are some links that you may find useful learning about SQL Server 20000 Failover Clustering
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/pro.../failclus.mspx
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default...lurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Microsoft SQL Server 2000 High Availability (MS Press)
ISBN 0-7356-1920-4
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx