July 13, 2016 at 4:26 am
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?
July 13, 2016 at 8:31 am
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.
July 13, 2016 at 9:11 am
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.
July 13, 2016 at 10:58 am
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.
July 15, 2016 at 9:51 am
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.
July 19, 2016 at 6:37 am
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.
July 19, 2016 at 12:33 pm
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.
July 21, 2016 at 4:39 am
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:
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply