March 16, 2011 at 7:45 am
Hi I am new to SSIS and i have the list of Host names around 500 and i need to connect to all the SQL Server Hosts and i need to get the all the instances running on the machine for each host names and store them in a table . Is there any way of doing that ?if so can some one shade light on it by step by step .
Thanks in advance
March 16, 2011 at 9:29 am
You can either add them all to sys.servers, and loop through that, or you can take a look at Red Gate's Multiscript product and see if that will do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 9:35 am
I think he has a list of hostnames and he needs to discover the SQL Server instances in the first place...RamSteve, can you clarify?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 2:57 pm
I Have the Host names around 500 and i need to loop to each host name and get the data of information like Host name,IP address,OS_Version ,Instance Names,Dbms_version,Port_number for each instance .
I can do this for single Host name by using WMI query in SSIS .But i need to Loop to each Host names(around 500) in run time
March 16, 2011 at 3:14 pm
OK, super-high-level design of an SSIS package that will do what you need to do...
1. Add a Data Flow Task to your Control Flow. Inside your Data Flow add a Flat File Source to read your text file. Pipe the results into a Recordset Destination. Now you have your data in a structure you can use.
2. Back in your Control Flow add a Foreach Loop so you can loop over the recordset you stored in a variable in previous step. Connect your Data Flow to the Foreach Loop. Inside your loop container you can call your WMI script once for each server name you picked up from your text file and do what you need to do with the results from there.
edit: clarify that you'll reference your recordset variable in your Foreach Loop container
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 8:43 am
I know I'm a bit late to the party on this one - came across this while searching for another issue - But in case you are still looking -the quest SQL Discovery wizard will do exactly what you are asking to do. It will discover by domain or IP range and will gather all of the information you are looking for and a bit more. It even has a blank password checker. And it's a free download[/url]. It's a great little tool to help keep you from walking into a buzz saw on the first day at a the new job..
--chris
June 20, 2011 at 8:41 am
Just iterate on all host and make dynamic connection for each host you have using Script Task Editor.
ie.
Dts.Connections["DBConnection"].ConnectionString = "Data Source=" + Dts.Variables["YourHostnameVariable"].Value + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";
Then just use DBConnection to grab data for each server and store them.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply