January 3, 2008 at 3:38 am
I just migrated to SQL2005 and can't find any functionality in the management studio or anywhere else for SSIS and DTS packages. I've use the wizard under the Legacy folder to migrate DTS packages. It indicates everything came over, but I don't see the objects anywhere. I can't find the interface or see the DTS packages anywhere.
I've also downloaded the Microsoft SQL Server 2000 DTS Designer Components and can't find any added functionality to the application.
I'm missing something that should be rather obvious...can someone point me in the right direction. It might be that I'm just an idiot, but it's like the SSIS didn't get installed or something (but I can start/stop the service)
I appreciate any feedback you can provide.
Marc
January 4, 2008 at 2:02 am
After upgrade SQL 2000 DTS components are deleted. Yoi should do following:
1. Add or Remove Programs in CP
Repair SQL Server 2005 Backward Compatibility feature
2. Download and install following package:
SQLServer2005_DTS.msi.
Afterwards you will be able to open package using old designer.
Now you can run old packages in legacy mode but Microsoft recommends to migrate them to SSIS.
You can migrate usning migration wizard but afterwartds you should check created SSIS package. This migration tries to map old components to new once but very often it doesn't work perfectly.
After migration you should consider to rebuild Packages from scratch.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
January 4, 2008 at 7:04 am
SSIS packages are not stored within the main database engine as they are in SQL Server 2000. You have to login (connect) to the Integration Services Engine and your DTS packages should be their.
January 4, 2008 at 8:24 am
Sliv03 is correct on where to find your missing SSIS packages. SQL 2005 works differently than SQL 2000 in this regard. You no longer have a folder under your database engine listing SSIS packages like you did with DTS under SQL 2000. From SQL Server Management Studio, you must hit "connect" and connect to the Integration Services engine. Then when that appears in your tree list, expand that and you will see your SSIS packages probably under the MSDB folder depending on how you imported them.
Now, by the same token, you also no longer have the ability to create / edit SSIS packages by right clicking on the object from your SQL Server Management Studio. To create a new SSIS package from scratch or to edit the source code for an existing package, you have to make use of either the included Business Intelligence Development Studio or Visual Studio 2005 (sold seperately).
Once you've built your package in BIDS or VS, you have to compile it by running the "build" command and then deploy it to SQL Server. My personal preference on deploying is to do an "import" from the Integration Services MSDB folder in SQL Server Management Studio and choose the security option "rely on server storage and roles for access control". This allows you to specify database level security for accessing / running your SSIS packages.
At this point you can easily create a scheduled job for your SSIS package by right clicking and selecting "New Job" from the "SQL Server Agent" icon under your database connection tree in SSMS. Just make sure the user specified to run as the SQL Server Agent service has the rights to run SSIS packages or you will get an error when the job runs.
That's really all there is to it. Good luck!
January 4, 2008 at 10:11 am
Another note, since there was a recent thread from someone who was confused about the use of the Package Migration Wizard. It converts DTS packages to SSIS packages rather than copies DTS packages to a SQL 2005 instance as legacy DTS packages.
Greg
Greg
January 7, 2008 at 12:59 am
Good point Greg. I would add it tries to convert. When there's mapping between DTS and SSIS component it replaces old with new one (there are some exceptions) if not it packs DTS into "Run DTS 2000" Component.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
January 9, 2008 at 3:50 pm
We are having a similar problem.
SQL Agent is running as a service
SSIS IS NOT running as a service
We can create, run and save SSIS packages
We can import and export to/from any table to any format
We can schedule queries and procedures and they run fine
We can schedule SSIS packages, but THEY FAIL
help please :0)
Kenny
January 10, 2008 at 11:39 am
And what are the circumstances/messages associated with package failure? If Integration Services is not running, you can't execute packages. Are the packages saved on the server where SQL Server and Integration Services are installed?
Greg
January 11, 2008 at 1:42 pm
I thought that all options were chosen during install.
I went again to install through CP, it think it replied that SSIS
was already installed. After reboot now change.
January 14, 2008 at 5:30 pm
We can create, run and save SSIS packages
We can import and export to/from any table to any format
Does that mean that SSIS is installed ?
SQL Agent is running as a service
SSIS IS NOT running as a service
We cannot choose to run it as a service
February 1, 2008 at 8:24 am
Which Edition of SQL 2005? I just found out recently that my Workgroup Edition won't run SSIS as a Service, which means I can't schedule my SSIS packages as SQL Agent jobs. (Well, at least not the way everyone using the other editions does it.)
February 1, 2008 at 8:30 am
I believe you need either the developer edition or enterprise edition.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply