December 28, 2009 at 11:57 am
Hi,
I need to come up with a way to gather information from sp_spaceused of all databases sitting on diff servers and store the results in a db in server [A]. The SSIS package has to execute on A and store results of execution on A too. No executon should take place on any other server besides A, all other servers are production servers.
This is what I have done so far.
1) Created a Execute SQL Task that queries a table srvname on server A.This would return list server ip add, username, password for servers to be checked. Prob : Some servers use windows and others SQL authentication. How do I strore this ?
2) Results of ip add, username and password is saved as 3 separate variables of object data type.
Im looking for some advice on how I can accomplish this task. How can I dynamicly build the oledb connection manager using the 3 variables[ ip add, username, password] taking into consideration if its windows/ sql authentication and pasing through a for loop that would loop thru each connection manager and gather sp_spaceused results and dump to a table on Server A.
December 28, 2009 at 2:16 pm
This series of articles details how to remotely collect server info:
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/61621/
December 28, 2009 at 2:40 pm
Thanks Old Hand, Im gonna dig in to this and try.
Shall keep posting if any doubts
December 29, 2009 at 8:47 am
Im trying something similar to my previous problem.Get list of database name from each server based on list of server name from a table and dump results to flat file.For testing purpose I only have 1 entry in serverlist table that uses SQL authentication to connect.
Here is my problem.Everything run's well no errors BUT no results are captured and upon opening MultiServer connection manager, no entry in password field even when save password is ticked.
1) Create table serverlist [name,ipadd,active,username,password,connectionstring]
2) Create a SQL Task 'select connectionstring from serverlist where active=1'
3) Store results in var ConString of type object
4) Create for each loop,Enumerator= Foreach ADO, ADO source variable= ConString, variable mapping= SrvCon of type string Index=0
5) Create data flow task within For Each Loop.
6) In data flow task, Define Olde db source, where ole db connection mng = MultiServer, access mode = SQL Command ,SQL Command Text = use master
select name from sys.databases
7) From ole db source to flat file connection manager as destination
Configuration for MultiServer oledb connection manager
1) Expression connection string = var SrvCon
Connection string in table serverlist is saved as such
Data Source=111.111.111.111;User ID=user;Password=pass;Initial Catalog=master
December 29, 2009 at 8:56 am
An update, when I pick MultiServer in the dropdown of servers in Oledb Source within the data flow task, which is within the for each loop.
I get the following msg
Error at Data Flow Task [OLE DB Source [1]]: The AcquireConnection method call to the connection manager "MultiServer" failed with error code 0xC0202009.
Error at DbCapacity [Connection manager "MultiServer"]: An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'user'.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Invalid connection string attribute".
December 29, 2009 at 2:41 pm
I have solved this by using servername instead of connection string in expression field of my oledb conn mng
December 9, 2010 at 10:29 pm
Hi All,
I am trying to get the details of new databases created on a weekly basis. My Multiserver connection manager is not working, throwing the below error.
Test Connection failed because of an internal error in intializing provider. Login timeout expired
An error has occured while establishing a connection to the server. when connecting to sql server 2005,
this failure may be becuase by the fact that under default settings sql server does not allow remoted connections.
Named pipes provider: Could not open a connection to sql server [53]
SQL Server Version: 2005 Developer edition
Services enabled and running : SQL Server, SQL Server Browser, SQL Full text search
Network settings: TCP\IP, Named Pipes, Shared memory
--------------
Followed the below steps.
1. Created a sql server lists table with sql full names.
like row1 KRN045
row2 KRN045\TEST
2. Populating the ADO variables using execute sql task with full result set. Result variable is SQL_RS.
Scope: Package
Data Type: Object
Value: System.Object
Connection manger: Database: Monitor, Server: KRN045, SQL- Select SQLFullName from tblvwSQLServer
3.Right click on the empty panel for the Control Flow Tab. Select Variables. Then select the Add Variables icon and add the following two variables.
SRV_Conn
Scope: Package
Data Type: String
Value: KRN045
4.Right Click in Connection Managers Panel
* Select New OLE DB Connection
* Click New
* Enter Server name that has the KRN045
* Click OK
* Click OK
* Right Click on the newly added connection
* Select Rename
* Enter MultiServer
Now we need to customize this connection
* Right Click on MultiServer
* Select Properties
* Change the Following Properties
*
o Expressions click ... box
o
+ Click Property
+ Click Drop Down Arrow
+ Select ServerName
+ In the expression box type @[User::SRV_Conn]
+ Click OK
o Initial Catalog change to Master
Getting the above error when i try test connection.. Please let me know where i am going wrong.
April 13, 2011 at 12:26 pm
Hi, were you able to reslove the MultiServer issues? If so can you elobrate how you did it. I am having the similar issue.
February 19, 2014 at 2:38 pm
I get the below error at Step7.
Error at Central Pacakge Database Properties [Connection manager "MultiServer"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply