Hello Experts,

We've 2008 R2 SSIS packages and we are able to run from SQL Server 2014 with the DTEXEC path of 100.

"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"

Now, we want to run same 2008 R2 SSIS packages from same SQL Server 2014 with the DTEXEC path of 120, here:-

"C:\Program Files\Microsoft SQL Server\120\DTS\Binn\DTExec.exe"

But jobs are failing, however jobs are running successfully if we upgrade 2008 R2 packages to 2014 with Upgrade Wizard.

Question,
Can we run the jobs with 120 DTEXEC path WITHOUT upgrading to 2014 packages?

Please help me out.

Thank You

Regards,

I think you answered your own question, no you cannot and should not.

I do not see why would you have SSIS 2008 around having the 2014 (much better) version.

Hey there,

Thanks for your quick response.

Reason are as follows:-
1. Upgrade Wizard doesn't work smoothly as we expect, it is having issue with Script Component to upgrade with DLL assembly, after upgrade we've to open Script Component manually and fix the DLL property manually, this is very time consuming process where we've have more than 1000 packages to upgrade and open all of them who has Script component to fix DLL assembly issue.

2. As per our organization we want to upgrade to SQL Server 2014 with one by one steps instead of doing everything together where our release methodology takes more than a months to push through all the environments with changes on DTEXEC job as well as package (with Script Component) changes.

Hope that helps

Thanks again for your response.

Regards,

gk03

Re #1: I confess I mostly seen issues with the Script Tasks or Components, too, but there is no choice, perhaps you went too far with the Scripts using DLLs in so many packages. So it is more like a bad architectural decision, but you have to upgrade.

Re #2: May it is time to utilize this time to depart from using DLLs in so many scripts.

Sorry for repeating the word time so many times, but I can feel your pain. Unfortunately, the right road is often the narrow one.

Please see:

https://msdn.microsoft.com/en-us/library/Bb522577(v=sql.120).aspx

Hi gk03,

From the document, we can know that we can run SQL Server 2008 R2 Integration Services packages by using the SQL Server 2014 version of the dtexec utility. When you use the version of the dtexec utility that comes with SQL Server 2014 Integration Services (SSIS) to run a SQL Server 2008 R2 Integration Services (SSIS) package, Integration Services temporarily upgrades the package to SQL Server 2014 Integration Services (SSIS). However, you cannot use the dtexec utility to save these upgraded changes.

After testing the issue in my environment, I can also execute the 2008 R2 package successfully via using the SQL Server 2014 version of the dtexec utility. So the issue shouldnt be related to the dtexec utility version. Maybe there are some other issues for the job, please use the Log File Viewer as well as any logs and debug dump files that were generated during the execution of the package to troubleshoot these issues. For more details, please see:
https://technet.microsoft.com/en-us/library/ms141701(v=sql.110).aspx

Thanks,
Katherine Xiong