dts to ssis

I migrated 2 DTS packages to SQL 2005 SSIS packages using the DtsMigrationWizard.exe, everything came through ok (), but my question is (now I'm not a DBA person by anymeans, I'm a .NET developer who just took on this project) when I open Server management studio for SQL 05, I don't see the job. How do I see the job in SQL 05 and either execute it or schedule it to run?

You need to connect to the Integration Services service - it's one of the server types in the Connect to Server dialog.|||

and you can do it only connect to Integration services on the Server locally, you cannot do it remotely.

|||

phdiwakar wrote:

and you can do it only connect to Integration services on the Server locally, you cannot do it remotely.

Not sure what you are saying there - you can use Management Studio to connect to an instance of Integration Services on another machine.

Could you clarify?

|||

OK, I connected, which option do I select? I see nothing under running packages(obivous, not running), nothing under stored packages --> file system, and when I click the Stored Packages --> MSDB I get an error message:

Failed to retrieve data for this request. (microsoft.sqlserver.smoenum)

The SQL Server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instanece of SQL server on the computer.

So do i need to install a default instance of SQl 2005 on my box? I'm running SQL 2000 adn SQL 05 on the same box, so how will that work?

|||

I got it to work, I had to edit the ssis xml file.

|||

IGotyourdotnet wrote:

I got it to work, I had to edit the ssis xml file.

Please indicate here the changes you made to that XML file so that others can resolve the same issue.|||

In the

MsDtsSrvr.ini.xml file you need to add the sql server instance:

<?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\instance</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

then you have to restart the SQL Server for that instance and SQL Integartion Server service. This will allow you to connect to it via Server Management Studio (SMS)

|||

jwelch,

When I open Integration Services on my desktop to connect remotely, I only have the "Windows Authentication" enabled. And since my login on my desktop is different from the server, I can't connect to it remotely. This may be the case for many people as well.

I cannot use SQL authentication, can I?

|||

phdiwakar wrote:

jwelch,

When I open Integration Services on my desktop to connect remotely, I only have the "Windows Authentication" enabled. And since my login on my desktop is different from the server, I can't connect to it remotely. This may be the case for many people as well.

I cannot use SQL authentication, can I?

No. You have to use Windows Authentication, because there is no such thing as SQL Authentication for the SSIS service. The SSIS service is separate and distinct from SQL Server.|||

Thanks, Phil for the quick response. In that case, connecting locally to IS on the SQL Server is the only way. I cannot and probably many people cannot connect to it remotely.

|||

phdiwakar wrote:

Thanks, Phil for the quick response. In that case, connecting locally to IS on the SQL Server is the only way. I cannot and probably many people cannot connect to it remotely.

Why? Have the admins of that server set up your account. Quite simple, really, and part of the process that needs to happen if you want to be able to use SSIS remotely.|||No, you can't use SQL Authentication. However, you can use the Run As.. to run Management Studio under an account that has permissions to access it.|||

Thank you guys for your advice. I did not realize those paths.

So jwelch, how do I do the "Run As" for Management Studio ?

|||

phdiwakar wrote:

Thank you guys for your advice. I did not realize those paths.

So jwelch, how do I do the "Run As" for Management Studio ?

Right-click on the shortcut to Management Studio, and choose Run As.