Connecting to instance of sql with SSIS

[Luvsql]

I have modified the MsDtsSrvr.ini.xml file to add the instance of sql 2005 (my default is sql 2000 and am slowly migrating all databases to 2005).

<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.\MyInstanceName</ServerName>
</Folder>

I restarted the SSIS service. I still cannot connect to it from management console. I've tried the browse but it only sees the database engines. Do I type in servername\instance name in the Server Name under Server Type integration services?

 

[mrdenny]

You need to change the service type that you are trying to connect to from Database Engine to Integration Services.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Luvsql]

Hi I have done that, but when I enter our instance name, I get an error.

TITLE: Connect to Server
------------------------------

Cannot connect to WFWSQL\WFWSQL2005.

Invalid server name "WFWSQL\WFWSQL2005". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

 

[RiverGuy]

I believe you can only have once instance of SSIS per server. So remove the \InstanceName part. Remember that the SQL Server Database Engine, SSIS, SSRS, SSAS are all technically different products. SSMS is just a tool that allows you to connect to different SQL Server products. So your database engine connection need not match your SSIS connection.

 

[Luvsql]

My default installation is sql server 2000 (and SSIS didn't exist then). I can't have the SSIS exist in that instance. The knowledge bases out there say to modify the xml file to tell SQL to use the instance instead of the default.

Does this mean I can't use integration services at all? That's retarded!

 

[mrdenny]

You can still you SSIS. SQL 2005 is installed as a named instance. SSIS is installed as a default instance. They are two separate services that run on the server.

When you connect to the SSIS instance on the server simply enter the server name and select Integration Services from the "Server Type" drop down.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Luvsql]

I tried entering the default server name but keep getting an "access denied error." My domain account is a domain administrator, as well as a sql sysadmin.

 

[Luvsql]

I added my domain account to all the permissions of the MsDtsServer Properties of the DCOM on the server and still cannot access it. Makes me miss DTS.

 

[mrdenny]

If you are getting an access denied error then you are hitting the SSIS instance. Fire up profiler against the SQL 2005 instance and see if it's throwing the access denied error, or if SSIS is for some reason.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog