Thursday, April 2, 2009

SSIS and SQL Server Named Instance issue

Problem Description

There are deployment issues when we try to deploy SSIS package on to a named instance SQL server with no default instance of it and store it in the SQL server. The errors that basically come out are the timeout errors. Even the event viewer would shows the same error.

Solution Description

SSIS runs as a service, and only one instance of SSIS can run on on platform. If we are working with one Named instances of SQL Sever the SSIS service will by default use the default instance to store packages (if we chose to store in the SQL sever). If we don't have a default instance or if we want to use a different SQL server to store your packages, we must alter the SSIS configuration file. we can find the file in: the SQL Sever install directory \90\DTS\Binn (e.g. c:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml) we change the ServerName attribute and this should work. Check the image for the same.

Let me know if this post helps anyone.

No comments:

Post a Comment