July 27, 2010 at 8:25 am
I have a sql query and I want to run the query against 10 different SQL 2000 servers.
The server names are present in a database table.
Basically the query should run for server1 and store the output in tableA, then the same query should run for the server2 and store the output in tableA, then the same query should run for the server3 and store the output in tableA etc until we finish running for all the 10 SQL 2000 servers.
I did the following using SSIS and this works fine if I run the SSIS against SQL 2005 and 2008, but not against SQL 2000.
Below is the error I am getting if I run the SSIS package against SQL server 2000:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "ConnectionManager1" failed with error code 0xC0202009.
[SSIS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
I am wondering if I have to do this using vb script in scripting task? Any ideas! Thanks.
Here is the step by step I did in SSIS:
1. Added Execute SQL task
Created two variables:
1. Variablename = ServerName
datatype = object
value = System.Object
2. variablename = var2
datatype = string
value = server1
Within the Execute SQL task editor used:
ResultSet = Full result set
Connection type = OLE DB
SQL statement = select ServerName from dbo.Serverinfo
Resultset:
Resultname = 0
VariableName = User::ServerName
2. Connected the Execute SQL task to the ForEach Loop Container task.
Within the Foreach Loop editor:
Collection:
Enumerator = Foreach ADO Enumerator
Enumerator configuration:
ADO object source variable: User::ServerName
Enumeration mode: Rows in the first table
variable mappings:
Variable = user::var2
Index = 0
3. Placed the data flow task inside the ForEach Loop container task.
OLE DB Source: SQL Query
OLE DB Destination: tableA
In the sourceconn manager:
DelayValidation = True
Expressions:
Servername = @[User::var2]
July 27, 2010 at 10:12 am
can you post how you are running the SSIS against sql 2000? Are you running the SSIS graph from your local machine and the connection settings pointing to the SQL 2000 server?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
July 27, 2010 at 11:38 am
Build ConnectionString proptery of DB source connection via expression with full details like Server, instance (if you have) and Database name, user id and pwd(if u r using), dynamically, means with ur for loop dbconnection shud be different each time.
basically, it shud point to different database server\dbname each time to read data....
Assumed that tableA is there in every database in all ur servers and its ur target table ...
July 28, 2010 at 12:18 pm
Pavera22 is on the right track, but I think that overall this is a lot simpler than you are making it.
As I see it you need an exec sql task, a foreach loop, and a dataflow container.
The exec sql task gets the server list and puts it into a variable of type object. The foreach loop uses that variable to execute the dataflow container for each server in the list and put that server name in a string variable.
The connection string is manipulted by an expression using the servername.
I'd also set delayed validation on the components for good measure.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply