Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Connection Manager Expand / Collapse
Author
Message
Posted Monday, December 28, 2009 11:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 413, Visits: 1,305
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.
Post #839545
Posted Monday, December 28, 2009 2:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 10:41 AM
Points: 414, Visits: 2,860
This series of articles details how to remotely collect server info:

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/61621/



Post #839589
Posted Monday, December 28, 2009 2:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 413, Visits: 1,305
Thanks Old Hand, Im gonna dig in to this and try.

Shall keep posting if any doubts
Post #839600
Posted Tuesday, December 29, 2009 8:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 413, Visits: 1,305
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
Post #839876
Posted Tuesday, December 29, 2009 8:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 413, Visits: 1,305
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".
Post #839881
Posted Tuesday, December 29, 2009 2:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 413, Visits: 1,305
I have solved this by using servername instead of connection string in expression field of my oledb conn mng
Post #840087
Posted Thursday, December 09, 2010 10:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 21, 2013 9:35 PM
Points: 15, Visits: 68
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.
Post #1032835
Posted Wednesday, April 13, 2011 12:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 06, 2014 4:59 PM
Points: 20, Visits: 167
Hi, were you able to reslove the MultiServer issues? If so can you elobrate how you did it. I am having the similar issue.
Post #1093087
Posted Wednesday, February 19, 2014 2:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 1:37 PM
Points: 43, Visits: 146
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.".

Post #1543230
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse