Is it possible to use SSIS 2014 on SQL2008R2 ? By installing any of SSIS2014 redistribution components on SQL2008R2
-
Hi All,
We developed SSIS Packages in Visual Studio 2013 for SQL Server 2014 Version and we are running those SSIS packages from Jobs at SQL Server 2014 (Like as shown below). Everything is running and going well.
Now we got a requirement that we need to run those packages on SQL Server 2008 R2. I simply copied those packages in SQL Server 2008 R2 Machine and tried to create a job same like in 2014. but here I got an error as "Unable to load the package as XML because of package does not have a valid XML format".
Here do we have any solution to execute 2014 Version SSIS package on SQL Server 2008 R2?Or Do we have any option to install only 2014 SSIS
- Redistribution components on SQL Server 2008 R2?
- Please suggest me if any know any information on this.
Note: Here are few points from my end
1. Management don't want to create the packages from 2008 as it's extra & duplicate efforts
2. Client don't want to upgrade his machine to 2014 as there are other application running on 2008 R2
3. I didn't find any solution / official support from MS.
Thanks in advance
Venkat -
As far as I know, you are out of luck & will have to develop the packages again in the old format.
SSDT 2015 allows the targeting of all versions back to 2012, but that's as far back as it goes.If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
It is not possible to install only SSIS 2014, though it is perfectly feasible for you to install an instance of 2014 which is dedicated to SSIS and use that.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Is there a reason you can't create a version connected to your 2008R2 instance but running on your 2014 instance?
-
Hi All,
Thanks to every one for your valuable inputs .
Scenario: We have developed SSIS packages in SQL Server 2014 version and all the packages are running successfully. Now we have a challenge that our old clients also want to deploy these SSIS packages in their machines, but they are still running with SQL Server 2008 R2. And they don’t want to migrate to 2014. Now here the main question for our tech team is
How to execute SSIS 2014 packages from the SQL Server 2008 R2 ?
As we did some R & D on this here we have few points which will help to us
1. There is no backward compatibility for 2008 R2 from any upper edition
2. There is backward compatibility in SQL Server 2015 to 2016/2014/2012 as target version
3. Changing version numbers alone in the SSIS packages will not work, as we need to also change the code and there is no 100% accuracy
4. Or we have to develop all the packages in 2008 – it’s tuff, duplication of work and time wasting process (from the Management & Developer point of view)
So, after long discussions between us & we come to know that, we can do this by installing only SSIS 2014 (as shown in below image - SQL Server 2014 Developer Edition) on the same machine where SQL Server 2008 R2 is installed.
After installing this we tried to execute the SSIS 2014 version package from command prompt as shown below. But still we are getting incompatibility errors. (here SSIS packages is developed in SQL SERVER 2014 version and the Data is pointed to the SQL Server 2008 R2 version )
DTEXEC /FILE "E:\TVN\PRISMA ETL 2014\TempDate_Load.dtsx"
But If I run the same package from the visual studio it’s running and I can see the updates on respective data base.
We got to know that by default DTEXEC is trigging from the 2008 as system taking it as default.
Now we executed the same package from SQL Server 2014 installed path (as shown below ) and completed successfully
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\DTEXEC /FILE "E:\TVN\PRISMA ETL 2014\Main Package.dtsx"
Now we have to configure this in Jobs at SQl Server 2008 R2, did this as shown below and working fine.
It will run only from Type as “Operating System” optionThanks,
Venkat -
Well done on working things out, but I do have a couple of comments:
- You did not just install SSIS 2014, you installed SQL Server 2014 with SSIS 2014. SSIS cannot be installed on its own.
- SQL Server Development Edition should be used only for, err, development. Anything else and you are violating the terms of the licence and are opening your client to the possibility of a large fine, in the event that they are audited by MS.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Another workaround which avoids the use of developer edition and the licencing issues around that is as follows:
1. Install SQL Server 2014 Express edition on the host, this will place the binaries required to run dtexec, you can disable all the services.
2. Place your ssis packages in a suitable location i.e. D:\SSDT\Packages
3. Create a batch file to run your package, for example:"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\dtexec.exe" /f "D:\SSDT\Packages\MyPackage.dtsx" > D:\SSDT\Logs\MyPackage_Example_Process_log.txt
4. Setup a SQL Agent job to call the batch file as an operating system task.
We have something similar setup to allow the execution of packages on a EC2 instance hosted in AWS where we have no need for a full SQL server (we use task scheduler for automation). There are some limitations as to what tasks work but for our needs it works well.
MCITP SQL 2005, MCSA SQL 2012
-
RTaylor2208 - Wednesday, September 6, 2017 9:15 AM
You have quoted a path to dtexec.exe which is for SQL Server 2012, I think.
Everything I have read suggests that it is impossible to run custom SSIS packages using dtexec with only SQL Express installed. Are you certain that your solution works this way?If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Sorry yes, this particular example is for SQL 2012, but the same works for SQL 2014. We have been running this very setup for almost 2 years. As I say it has its limitations in what tasks will work within a package, but the most common execute SQL \ DFlow tasks work.
MCITP SQL 2005, MCSA SQL 2012
-
RTaylor2208 - Thursday, September 7, 2017 2:10 AM
Interesting. Thanks for the info.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply