Connecting to SSIS in Management Studio.

Hi:

I have 4 named instances of SQL 2005 running on one of our sales server (dont ask me why I have 4 instances.Its a beefy box btw). All the instances have SSIS Packages (around 6-7 in each instance) saved to the SQL server and not to the file system. The issue is every time i need to look at the packages or export the packages from SSMS I have to edit the MsDtsSrvr.ini.xml and type in the named instance name within the <ServerName></ServerName> tag . I then have to restart my SSIS. I dont see an easier approach to this method.

This is causing me a lot of unnecessary time waste. is there anyway this can be automated where in i can pass the instance name dynamically to the ini file or even more best, can I have all the instance names in the ini file and some how look at the packages in each Instance. I am not sure how having all the instance names in the ini file woud resolve the issue though.

I know I can use BIDS which is much more flexible and a recommended approach but need a solution for looking at SSIS packages through SSMS in all of the 4 instances. I look forward to recommendations from anyone who have better ideas and suggestions.

Thank you

AK

you could create a console app that edits the xml file and re-starts ssis.|||

Thanks Duane. Do you happen to have an example code sample on how to do that?. I can build looking from that.

Thanks again

AK

|||

Ankith wrote:

Thanks Duane. Do you happen to have an example code sample on how to do that?. I can build looking from that.

Thanks again

AK

unfortunately, i don't have a code example to share with you. however, writing such an application should be fairly straightforward (provided that you know .NET). the .NET system.xml namespace provides a number of classes to manipulate xml data. also, the system.serviceprocess namespace provides classes to manipulate windows services.

i hope this helps.

|||

Hi Duane:

I got this working. I am posting the code snippet for the benefit of others. I have created a SQL Agent job that changes the server name in the XML ini file and restarts SSIS. Restarting I do it through the Job step. Works great.

if (File.Exists(xmlFile))
{
XmlDocument doc = new XmlDocument();
doc.Load(xmlFile);

XmlNode node = doc.SelectSingleNode(@."//*[local-name()='ServerName']");

if (node == null)
{
Console.Write("Node does not exist");
}
else
{
node.InnerText = value;
}

node = null;

doc.Save(xmlFile);

Thanks again for your help.

AK

|||Instead of switching between the servers, you can simply add them all to the SSIS service config file! Simply copy the <FOLDER> tag (till the end - </FOLDER>) as many times as you have SQL instances, give each one unique <NAME>, save config file and restart the SSIS service:

...
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>Sql1</Name>
<ServerName>.\Instance1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>Sql2</Name>
<ServerName>.\Instance2</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>Sql3</Name>
<ServerName>.\Instance3</ServerName>
</Folder>
</TopLevelFolders>
...|||

WOW!!!. Thats cool. Thanks Michael.I used your solution and it works great and I dont need my code anymore. DBAs who dont have much experience with C# will find your method really helpful.

Thanks for posting it.

Best Regards

AK