I have a SSIS package that works fine from VS2015 but when I try to run it from SSMS it fails with the error:
To run a SSIS package outside of SQL Server you must install conditional split of integration services or higher.
I did a little research and also created a new package with only a source and destination which worked fine. Once I added the conditional split it failed again. It seems like this may be a version conflict between the data tools, SSIS and SQL Server. I have other packages with conditional splits and other tasks that are executed in SQL Server such as Execute SQL tasks. The only reason I can think that they work is because they were created in a previous version of SSIS then upgraded.
I am using Visual Studio 2015 Community with
SQL Server Data Tools 14.0.61021.0
SQL Server Integration Services Designer 13.0.1701.8
On the SQL Server it says the SSIS version is 12.0.2269
My version of management Studio is 12.0.2000.8
I am just having a hard time finding out what versions are required to work. Do I need a different version of SSIS to coincide with SSDT?
It looks like the SSDT version I am using is for SQL Server 2016 but it is backward compatible. I set it to be compatible with SQL Server 2014 but it still doesn't work. Same error.
Any suggestions are appreciated..
What is the error you are getting?
SSMS does not have an SSIS component...
I believe you may be referring to maintenance plans in SQL Server. If so, maintenance plans are special types of SSIS projects with a minimal set of functions available. Maintenance plans are executed in a subsystem within SQL Server that does not know anything about the other functions that are available to normal SSIS packages.
To be able to execute SSIS packages on a server - that server must have SQL Server Integration Services installed. You can then deploy the packages to the server using several different methods. The preferred method for SQL Server 2012 and higher is to utilize the Integration Services Catalog - which needs to be installed and configured in SQL Server before it can be utilized.
Thanks for the response Yosiarz.
1.The package is located on a shared drive from a different machine. There is no issue reading the package because it is able to see the conditional split.
2. I am not trying to run the package from a job....yet. I am connected to integration services in SSMS and trying to run the package from there.
3. See #2 and skipping to #5
5. This is how I created the package when connecting to integration services in SSMS
Jeff - First, thank you for the response. I am not referring to a maintenance plan. I can connect to integration services through SSMS, import my package and run it from there. I have done it several times without issue. They were created in a previous version of BIDS though Now I am trying to do it using the data tools in Visual Studio.
Integration Services is installed on the server (version 12.0.2269). This is the version for SQL Server 2014. As I mentioned I am using the 2016 version of data tools but that is backward compatible so it shouldn't be an issue. I have tried deploying it as a SQL Server 2014 package and it still doesn't work. Deployment is something I am still a little sketchy on and I know it has changed a bit with the 2016 version of the data tools. I normally just save my package and then import the .dtx file to create my package through integration services in SSMS.
I have a feeling this is where my problem is and the first thing I will check on Monday. While the data tools are backward compatible and I am running SQL Server 2014. I wonder if the integration services has to be the 2016 version. I think with the new version you are importing the entire solution, not just the dtx package.
I am going to continue to do my research but if anyone has any other ideas it is greatly appreciated.
Scott
Okay - I see the problem...
When you access Integration Services this way - you are attempting to run the package locally on your workstation. It is not executing on the server.
To get the package/project to execute on the server you need to create a SQL Server agent job - or a script (powershell/batch/...) on the server that executes the command line dtsexec.exe utility.
To properly deploy and manage packages on a server as of SQL Server 2012 - you should install Integration Services Catalog and use project deployment to deploy full projects into the catalog. There are other options for deployment but that is the best method because you can manage project and package parameters from the catalog and/or the agent jobs.
You cannot connect SSMS 2016 to an earlier version of Integration Services - it is only compatible with the same version you are running on that instance of SQL Server. If the instance is 2014 you have to use the 2014 version of SSMS to connect to integration services. See this document: https://docs.microsoft.com/en-us/sql/integration-services/service/connect-to-a-remote-integration-services-server-ssis-service
For a tutorial on how to use Integration Services Catalog - and the project deployment model - see this article: https://www.mssqltips.com/sqlservertip/2450/ssis-project-deployment-model-in-sql-server-2012-part-1-of-2/ (2016 has additional features but the deployment model is the same).
When you execute a package from within the Integration Services Catalag - it then runs on your server instead of running on your workstation.
Jeff - I am not using SSMS 2016. I am using SSMS 2014, integration services on the server is version 12 which is the version for 2014. On my desktop in Visual Studio I am using SSDT 2016 which should be backward compatible.
How do you know the package is running on the workstation? I just tried importing the package again to the MSDB folder and it said the package already exists on the server.
Thanks for the link on integration services catalog and the project deployment model. I will look that over.
Using the legacy integration services is telling me that you are attempting to run that package on your workstation. You would have to have integration services installed on your workstation to run that package.
Please follow the guidelines for deploying a package to the server using project deployment. This will make it much easier for you to manage and maintain your SSIS projects. When you execute a package from the integration services catalog - it executes that package on the server and notifies you to review the report to see how it executed.
Thanks - I created the catalog and working on deploying the package. I am trying to work through some errors in the middle of a ton of other things.
The weird thing is it says that access is denied to the text file that is my destination but it is in the same location as my source file. Oh well, I will work through it.
Thanks for the help.
Those types of errors are usually configuration errors after deploying and running in a new environment.
RetainSameConnection setting for example on a connection to an excel/csv file will cause lock/access denied issues when SSIS fails midway
Another thing is under what user security context are you running this as? sa? domain.com/sqlserviceaccount?
These things usually comes to bite you with something that worked on your machine but now does not
Thanks for your answer last week. But one more thing, i have tried wrf to mp4, while the program mentioned that i must converted it to WMV first before conversion, why?
So I was able to get my test package working fine. Now when I try running my original package that was giving me the conditional split error I am getting an entirely different error:
If the 64-bit driver is not installed, run the package in 32-bit mode.
I did exactly that but it still doesn't work.
This is followed by an error saying that the call to my Excel connection manager failed which I am assuming is due to the first error.
too lazy to scroll up ....what arch is your server 32bit or 64?
64-bit
EDIT: I am assuming it is. Unfortunately that is a different team and in their infinite wisdom they have decided that we should not have remote access to that machine any longer. It is political and a sore subject. I am working on confirming it is 64
are you using any sort of 3rd party assembly in your SSIS packages?
I confirmed they are 64-bit
