Can't find SSIS ?

I have VS2005 and have been using SQL 2000. I installed SQL 2005 Developer but I can't find SSIS anywhere. It is hard to start even the first tutorial if I can't find the program. Was it supposed to be installed with 2005 Developer? Help!

Thanks in advance.

Chuck

Did you select to install Integration Services and the development environment. If you did, the best way to Start to is to open the Business Intelligence Development Studio (BIDS) - find it under your SQL Server group on the Start menu. See http://msdn2.microsoft.com/en-us/library/ms173767.aspx

Start a new Integration Services project ... http://msdn2.microsoft.com/en-us/library/ms141823(sql.90).aspx

If you do not have BIDS installed then you most likely did not install dev tools.

Donald

|||

Did you select SSIS during the install? Note that installing SSIS does not enable you to build packages, only run them.

The SSIS development tool (i.e. BIDS) is installed when you select the "Client Tools" option.

-Jamie

Can't find SQL Server: SSIS Pipeline performance object in perfmon for 64-bit Server

I can't find 'SQL Server: SSIS Pipeline' performance object in performance monitor on a 64-bit SQL Server. I see it on a 32-bit. Does anybody know why?

Thanks

The 'SQL Server: SSIS Pipeline' performance objects in perfmon don't work in x64. They simply don't appear when viewed from either an x64 machine or a x86 machine.

However, if you login to the x64 machine locally and use the 32 bit version of perfmon (C\WINDOWS\SysWOW64\perfmon.exe), you can see them, but even that workaround is not very useful when you have a significant number of SSIS machines you'd like monitored.

can't execute tasks after moving .dtsx to new server

Hello.

I have two servers, A for dev and B for production.

On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.

All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.

On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.

The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.

Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.

Can anyone help with this as my deployment to live isn't going very well to say the least!

Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.

Thanks.

bobbins wrote:

Hello.

I have two servers, A for dev and B for production.

On server A I developed a project containing a SSIS package using SQL Server business intelligence development studio. The package runs fine from the BIDS and also when I save it to SQLServer itself and run it as a scheduled job using the SQLServer Agent.

All ready to roll out to Server B I thought, so I then saved the .dtsx file to a shared network drive.

On Server B, I created a empty project with the same name as it had on Server A. I then imported the .dtsx file into the project using project > add existing item.

The package appeared to import ok but I now cannot execute any of the data flow tasks in isolation. If I right click on them, there is no option to 'execute task' as there should be, it is not greyed out, it's not there at all.

Also, if I attempt to debug the whole package I get a message saying 'This document is opened by another project'.

Can anyone help with this as my deployment to live isn't going very well to say the least!

Both server A + B are 32-bit 2005 std edition SP1 on W2003 Server std edition SP1.

Thanks.

The easiest way to redeploy SSIS Packages to a different server is to use the Deployment Utility: http://msdn2.microsoft.com/en-us/library/ms141190.aspx|||

Hi bobbins,

Thoughts / suggestions:

The 'document is opened by another project' error is interesting. First, make sure Business Intelligence Development Studio(BIDS) is shut down on your first server. Instead of creating a new empty project and importing the package, try copying the entire project folder to your shared drive and opening it from the BIDS on your second server. This sounds more like a file locking issue to me.

Hope this helps,

Andy

|||

Thanks for the replies.

I copied the entire project folder to the new server and it is ok. The only issue that arises is that some of the connection managers lose their login information, these are ADO.NET ODBC connections to an Ingres database, so I suspect this is just normal behaviour anyway.

Thanks again.

Can't Excute SSIS package

So everything was working great. Now I have a few problems. The little play button is no longer available for me to puch and when I try a right click execute package a get a "Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)". Does anyone know how to fix this? Also when I try and copy tasks from within my package I get a "An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)"
followed by "Could not copy object 'package name' to the clipboard.
(Microsoft.DataTransformationServices.Design)" and then "Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)"

Does anyone what might be wrong

Thanks in advance

good news is I have the same problem, bad news is I don't know why :s

If that can help, the details are

Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)

Emplacement du programme :

at Microsoft.DataWarehouse.ProgrammingByContract.State.NullCheck(Object obj)
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)

|||This seems to be a VS problem. I reinstalled Visual Studio and that fixed the problem. Well... at least it works now ;)
|||Yeah, my machine ran automatic updates and all of a sudden it came right.

Can't Excute SSIS package

So everything was working great. Now I have a few problems. The little play button is no longer available for me to puch and when I try a right click execute package a get a "Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)". Does anyone know how to fix this? Also when I try and copy tasks from within my package I get a "An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)"
followed by "Could not copy object 'package name' to the clipboard.
(Microsoft.DataTransformationServices.Design)" and then "Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)"

Does anyone what might be wrong

Thanks in advance

good news is I have the same problem, bad news is I don't know why :s

If that can help, the details are

Unexpected internal error: Invalid state for operation. (Microsoft Visual Studio)

Emplacement du programme :

at Microsoft.DataWarehouse.ProgrammingByContract.State.NullCheck(Object obj)
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)|||This seems to be a VS problem. I reinstalled Visual Studio and that fixed the problem. Well... at least it works now ;)|||Yeah, my machine ran automatic updates and all of a sudden it came right.

Can't create SSIS project in BIDS on workstation with Client tools only installed

I have the June CTP SQL 2005 client tools only installed on my workstation. All has gone well until I tried to create a SSIS project in BIDS.

I receive this message

TITLE: Microsoft Visual Studio

Error creating package

ADDITIONAL INFORMATION:

Failed to save package file "D:\Documents and Settings\SuzMSDN\Local Settings\Temp\tmp13E.tmp" with error 0x80040154 "Class not registered".

Failed to save package file "D:\Documents and Settings\SuzMSDN\Local Settings\Temp\tmp13E.tmp" with error 0x80040154 "Class not registered".

Tried registering DTSPipeline.dll per a post with no success.

I can log into the server where SQL is installed and use BIDS on the server without issue for an SSIS project.

Thoughts?

Susan

Does the dialog have "show technical details" button?
If yes, please post this information.

Thanks,
Michael.|||

Below are the technical Details ...thanks

Susan
===================================

Error creating package (Microsoft Visual Studio)

===================================

Failed to save package file "D:\Documents and Settings\SuzMSDN\Local Settings\Temp\tmp10.tmp" with error 0x80040154 "Class not registered".

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.DataTransformationServices.Wizards.Dts90WizardSaveNewTask.AddPackageToProject(Package package, String itemName)
at Microsoft.DataTransformationServices.Wizards.Dts90WizardSaveNewTask.CreateAndAddPackageToProject()
at Microsoft.DataTransformationServices.Wizards.Dts90WizardSaveNewTask.Execute(WizardInputs wizardInputs)

===================================

Failed to save package file "D:\Documents and Settings\SuzMSDN\Local Settings\Temp\tmp10.tmp" with error 0x80040154 "Class not registered".

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)

|||Let's try this:

regsvr32 msxml3.dll
regsvr32 msxml4.dll
regsvr32 msxml6.dll|||Michael,

Thanks you. That did the trick. I had to unregister msxml6 in order to take the MS SQL 2005 elearning classes.

So to take class you unregister...to work you register. ; )

Hopefully corrected for elearning in the next release.

Thanks for your assistance.

Susan

Can't create new SSIS package in VS 05

Is there anyone out there that can help me - please! I'm about to put my head through a wall.

I used to be able to create SSIS packages in VS 05. It's been a while since I've done it, but when I went in the other day, it would let me - all I can do is get errors. It tells me an "error prevented the view from loading" when it opens in control flow (blank packages and ones I created before all this started) and I get this error :

"Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (System.Windows.Forms)"

If I go over to the Data Flow tab and there are no errors until I click "Click here to add a new Data Flow task", then I get this :

"Object reference not set to an instance of an object. (Microsoft.DataTransformationServices.Design)"

So... here's what I've tried.

Uninstalled and reinstalled VS 05 and SP1 (this was before I realized the problem was with SQL).

Uninstalled and reinstalled Office 2003 Web Tools.

Made sure all the MSXMLs were registered.

Uninstalled and reinstalled all SQL Servers and Tools/Components.

Made sure all SQL Services were running under Local Account

Made sure Integrated Services service was running.

Applied all SQL patches.

I cannot get this to work and I need to be able to create packages. The only other step I can think of is wiping my computer and I don't want to have to do that.

Please please can anyone help me? I've been scouring the internet and working on this for 1 day and 1/2.

Miraculously - it's started working again. All I've done since then is go to the MS Update website and tried to add SQL 2005 SP2 again. It failed again, but this time when I opened my SSIS project, it opened ok.

I also just realized that I disabled the antivirus on my computer in an attempt to upgrade to SP2 again (although I haven't tried to upgrade the second time), but the antivirus is still off - When I reboot, I'll try to open it again - of it fails it must be the antivirus.

|||

Well, it wasn't the antivirus, because I've rebooted and it's still working - with the antivirus running. So if anyone else is running into this issue and is reading this thread for help - sorry - don't know what to tell you or how it fixed itself.

All I know is - you live by Microsoft, you die by Microsoft.

Can''t create a SQL Server package configuration

I've created the table [SSIS Configurations] and have added a dummy row to this with "XYZ" in the filter name.

When I use the package configuration wizard I can see that offered as a filter - so I know my connection is seeing the server ok.

The problem I get is when I get through to "Finish" and insert the configuration to the table - I get this error message and I can't find any help on it:

Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)

Could not complete wizard actions.

I'm connecting as system administrator and am just foxed as to why it won't insert ... Anyone got any pointers?

Many thanks

Matt

have you used SQL Profiler to watch what is being sent to the server?|||

*bump*

I am getting this error too.

Any ideas? Nothing odd in profiler.

|||

that is weird. If you are choosing a row that already exists in the configuration table; I think the wizard prompts you whether you wan to re-use the exiting row or not...do you see that prompt? what are you selecting?

|||

I've resolved my issue.

It was to do with a property value (an SQL string) that was too long to fit in the default configuration table data column (254 characters.)

I have moved to using an external xml file instead for the SQL statements.

I also tried making the column larger, but ultimately I think the more appropriate option for us is going to be external xml files.

Can't create a SQL Server package configuration

I've created the table [SSIS Configurations] and have added a dummy row to this with "XYZ" in the filter name.

When I use the package configuration wizard I can see that offered as a filter - so I know my connection is seeing the server ok.

The problem I get is when I get through to "Finish" and insert the configuration to the table - I get this error message and I can't find any help on it:

Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)

Could not complete wizard actions.

I'm connecting as system administrator and am just foxed as to why it won't insert ... Anyone got any pointers?

Many thanks

Matt

have you used SQL Profiler to watch what is being sent to the server?|||

*bump*

I am getting this error too.

Any ideas? Nothing odd in profiler.

|||

that is weird. If you are choosing a row that already exists in the configuration table; I think the wizard prompts you whether you wan to re-use the exiting row or not...do you see that prompt? what are you selecting?

|||

I've resolved my issue.

It was to do with a property value (an SQL string) that was too long to fit in the default configuration table data column (254 characters.)

I have moved to using an external xml file instead for the SQL statements.

I also tried making the column larger, but ultimately I think the more appropriate option for us is going to be external xml files.

Can't create a setup project for project referencing Microsoft.SqlServer.ManagedDTS

I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.

I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.

Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:

Dim oApp As New Application
Dim oPkg As New Package
oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
Dim oResults As DTSExecResult
oResults = oPkg.Execute

Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.

During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project.
C:\windows\system32\msxml6.dll

OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.

How do I find this out?

Has anyone else created a project like this and experenced the same?

I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.

Thank you.

I received the answer to the above problem here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1195465&SiteID=1

Can't create a setup project for project referencing Microsoft.SqlServer.ManagedDTS

I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.

I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.

Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:

Dim oApp As New Application
Dim oPkg As New Package
oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
Dim oResults As DTSExecResult
oResults = oPkg.Execute

Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.

During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project.
C:\windows\system32\msxml6.dll

OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.

How do I find this out?

Has anyone else created a project like this and experenced the same?

I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.

Thank you.

I received the answer to the above problem here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1195465&SiteID=1

Can't connect to SSIS

Hi,

I'm evaluating SQL server 2005 for planning a upgrade of our datawarehouse from SQL 2000 to 2005.

We use a lot of DTS pacakages so that's my primary focus right now.

I get this strange error when trying to connect to SSIS -> Stored Pacakges -> MSDB:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

BUTTONS:

OK

The install is on a x64 2003 with a x64 enterprise edition, with two instances. So what msdb is it trying to connect to since I can't seem to see this anywhere in management studio?

Initially installed the two instances without named pipe support (only tcpip), but the naitive client was configured for named pipe support. I have now enabled named pipe support for both instances as well and afterwards restarted the server, but the problem persists.

I've also checked the two instaces to verify that they allow remote connections, as well as doubling the query timeout period to 1200.

I managed to move via DTS backup (non ms tool) a package from a test server onto the 2005 instance under Management -> Legacy -> DTS. Then I ran a migration of the imported pacakge and that ran smoothly as well.

So now I would just like to see the result of the converted pacakage.

Any clues to what I do wrong?

Best regards

SUN

There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB. By default, it is set as '.' which means the local server name, default instance.

In your case, you probably have either a named instance, or are trying to connect to a non-default instance. Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

So, the part that I highlighted is what needs to be modified...just overwrite the '.' with the appropriate instance name and see if that works for you.

|||

Hi,

Thanks for the reply, Yes I installed both instances as named ones. So this seems to be the right solution. I'll test this out and see if it works on Monday. Thanks.

//SUN

|||

Tried the resolve and all worked well. Just make sure you type the instance with the server name ie:

{servername}\{instance name}

Typing just the instance name failed.

|||

More importantly after you make the change to the XML file. You have to make sure you stop and restart the IIS service(Control Panal->Administration Tools->Services). Only after doing this it starts working.

|||

Thanks for the info -

Why isn't this a config property through the management console or config manager?

|||

Having the same issue.

Tried to add server name, and server/instance to the file as suggested. Restarted IIS each time as suggest.

Still having problem connecting.

I can "see" and import into BIDS packages created by the Import/Export Wizard.

If SSIS doesn't support instances as suggested when trying to connect to SSIS, how to handle this. I have two instances of the DB, Development and Test. From there we would promote to production. But on the dev box, how would you suggest we have development and test versions of our SSIS packages?

|||

Thanks for the information.

Can't connect to SSIS

Hi,

I'm evaluating SQL server 2005 for planning a upgrade of our datawarehouse from SQL 2000 to 2005.

We use a lot of DTS pacakages so that's my primary focus right now.

I get this strange error when trying to connect to SSIS -> Stored Pacakges -> MSDB:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

BUTTONS:

OK

The install is on a x64 2003 with a x64 enterprise edition, with two instances. So what msdb is it trying to connect to since I can't seem to see this anywhere in management studio?

Initially installed the two instances without named pipe support (only tcpip), but the naitive client was configured for named pipe support. I have now enabled named pipe support for both instances as well and afterwards restarted the server, but the problem persists.

I've also checked the two instaces to verify that they allow remote connections, as well as doubling the query timeout period to 1200.

I managed to move via DTS backup (non ms tool) a package from a test server onto the 2005 instance under Management -> Legacy -> DTS. Then I ran a migration of the imported pacakge and that ran smoothly as well.

So now I would just like to see the result of the converted pacakage.

Any clues to what I do wrong?

Best regards

SUN

There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB. By default, it is set as '.' which means the local server name, default instance.

In your case, you probably have either a named instance, or are trying to connect to a non-default instance. Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

So, the part that I highlighted is what needs to be modified...just overwrite the '.' with the appropriate instance name and see if that works for you.

|||

Hi,

Thanks for the reply, Yes I installed both instances as named ones. So this seems to be the right solution. I'll test this out and see if it works on Monday. Thanks.

//SUN

|||

Tried the resolve and all worked well. Just make sure you type the instance with the server name ie:

{servername}\{instance name}

Typing just the instance name failed.

|||

More importantly after you make the change to the XML file. You have to make sure you stop and restart the IIS service(Control Panal->Administration Tools->Services). Only after doing this it starts working.

|||

Thanks for the info -

Why isn't this a config property through the management console or config manager?

|||

Having the same issue.

Tried to add server name, and server/instance to the file as suggested. Restarted IIS each time as suggest.

Still having problem connecting.

I can "see" and import into BIDS packages created by the Import/Export Wizard.

If SSIS doesn't support instances as suggested when trying to connect to SSIS, how to handle this. I have two instances of the DB, Development and Test. From there we would promote to production. But on the dev box, how would you suggest we have development and test versions of our SSIS packages?

|||

Thanks for the information.

Can't connect to SSIS

Hi,

I'm evaluating SQL server 2005 for planning a upgrade of our datawarehouse from SQL 2000 to 2005.

We use a lot of DTS pacakages so that's my primary focus right now.

I get this strange error when trying to connect to SSIS -> Stored Pacakges -> MSDB:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

Login timeout expired
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server [2]. (Microsoft SQL Native Client)

BUTTONS:

OK

The install is on a x64 2003 with a x64 enterprise edition, with two instances. So what msdb is it trying to connect to since I can't seem to see this anywhere in management studio?

Initially installed the two instances without named pipe support (only tcpip), but the naitive client was configured for named pipe support. I have now enabled named pipe support for both instances as well and afterwards restarted the server, but the problem persists.

I've also checked the two instaces to verify that they allow remote connections, as well as doubling the query timeout period to 1200.

I managed to move via DTS backup (non ms tool) a package from a test server onto the 2005 instance under Management -> Legacy -> DTS. Then I ran a migration of the imported pacakge and that ran smoothly as well.

So now I would just like to see the result of the converted pacakage.

Any clues to what I do wrong?

Best regards

SUN

There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB. By default, it is set as '.' which means the local server name, default instance.

In your case, you probably have either a named instance, or are trying to connect to a non-default instance. Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

So, the part that I highlighted is what needs to be modified...just overwrite the '.' with the appropriate instance name and see if that works for you.

|||

Hi,

Thanks for the reply, Yes I installed both instances as named ones. So this seems to be the right solution. I'll test this out and see if it works on Monday. Thanks.

//SUN

|||

Tried the resolve and all worked well. Just make sure you type the instance with the server name ie:

{servername}\{instance name}

Typing just the instance name failed.

|||

More importantly after you make the change to the XML file. You have to make sure you stop and restart the IIS service(Control Panal->Administration Tools->Services). Only after doing this it starts working.

|||

Thanks for the info -

Why isn't this a config property through the management console or config manager?

|||

Having the same issue.

Tried to add server name, and server/instance to the file as suggested. Restarted IIS each time as suggest.

Still having problem connecting.

I can "see" and import into BIDS packages created by the Import/Export Wizard.

If SSIS doesn't support instances as suggested when trying to connect to SSIS, how to handle this. I have two instances of the DB, Development and Test. From there we would promote to production. But on the dev box, how would you suggest we have development and test versions of our SSIS packages?

|||

Thanks for the information.