SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Migrating DTS from 2005 to 2008


Migrating DTS from 2005 to 2008

Author
Message
Lippy
Lippy
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 177
Any help with the below issue would be much appreciated.

I have to migrate a DTS package from a SQL2005 Instance to the SQL2008 instance which is replacing it on the same server..
The 2005 Instance is a default instance and is SQL 2005 64bit Enterprise.
The 2008 instance is named but is the only 2008 instance. It SQL 2008 R2 Standard 64 bit
The server runs under Windows 2008 R2 Enterprise
The DTS package runs under SQL Agent which operates under the same Windows admin account on both instances.
There are no issues with the Agent job on the 2005 instance.
I have tested all the DTS elements individually on the 2008 instance and they function correctly.
The agent job step which runs the DTS is the same in both SQL instances and uses the following command:

DTSRun /S "servername\instancename" /N "DTSPackageName" /E

This will not run returning the error:
the process could not be created for step XX of job ........... (reason 2)

Opening up a command window under Windows and running the code from there succeeds.
I have googled the error but do not seem to find anything relevant.
I have tried running Agent under the local system account as weel as the one that normally runs SQL and Agent, restarting between times and had no joy.

Can someone please advise where I am going wrong?
Smendle
Smendle
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 1174
My first question is did you load the 2005 package in a 2008 BIDS session? Sometimes doing this and saving the package, then reloading to the server will solve these upgrade issues.
Lippy
Lippy
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 177
Thank you for quick response.

Regarding BIDS Session, no we set up DTS and its editor on the 2008 instance. I was not aware that there was any method other than that or SSIS.

I am currently working on setting up an SSIS job to run the package in the hope of getting it to run that way.
Smendle
Smendle
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 1174
Yes install Business Intelligence Studio 2008 and then load your 2005 package into that IDE. Deal with any errors that the IDE reports then run test in the IDE and if all is well save the package and reload to SQL Server.
Lippy
Lippy
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 177
Thank you Smendle for your input. I am not sure that I have not got BIDS installed already since I can create SSIS jobs but am going to verify this along with some other approaches that have been suggested to me.

The last resort, of course, is to run the DTS from another SQL 2005 installation which sits on the same subnet and has access to the database.
Lippy
Lippy
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 177
OK, we differ in naming things. I double checked and BIDS is installed. It is what I use for managing and creating SSIS packages. When I create a package to run the DTS it has the same issue. There is a problem with permissions where the user that is running Agent and DTS is not permitted to execute.
Smendle
Smendle
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 1174
as this issue has been awhile your first reported error was...

the process could not be created for step XX of job ........... (reason 2)


As that may or may not be a permission issue I took it to be one of upgrading from 2005 to 2008.

Did you get any package errors on LOADING the 2005 package into 2008 BIDS?

If not, and you are still looking at permission issues than insure that whatever file system permissions are they are also transferred to the new system. I suspect now that you are write and reading from files not in a networked location but local to the SQL Server.
Lippy
Lippy
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 177
thank you for input Smendle.

I have found the issue - now all I need is time....it's a complicated suite of packages designed by a third party

The reason for the failures was not the installation but rather the nature of the DTS. The packages I am running are nested - so the first package calls another package as its second step; the second package then runs another few packages in turn before returning control to the first package which then runs another and so on. That worked fine on SQL Server 2000 and SQL Server 2005. When I run any of the packages individually they run but the nests do not.

I found the answer here thanks to Salekin Khan:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e192f6bb-471e-4193-b9cf-5bfefcde3158/how-to-schedule-legacy-dts-package-in-sql-server-2008?forum=sqlintegrationservices

and it said:

.....When you create a SQL Server Agent job to run packages, you must create a separate step for each package that you want to run


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search