Needless to say, this indicates the SSIS package didn’t load to the server. This is especially frustrating as this particular package worked fine in Visual Studio. Believe it or not this screen actually contains information which can be used to resolve the error.
Finding a Useful Error Message
How do you go about determining what to do? Click on the word Failed. This will bring up some useful information, although it may not appear that way at first. Here’s the error message I received
The message Failed to deploy project isn’t very useful, but the rest of the message is. The operation_messages view lives in SSISDB, and the operation identifier number is how to determine what the error is. Run this query, using the number provided in the error message, which in this case is 173
Select * from catalog.operation_messages where operation_id = 173
Here are the results from that query.
|50719||173||2016-02-29 15:02:08.2478928 -07:00||120||20||Failed to deploy the project. Fix the problems and try again later.:SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.||NULL|
Now this message is quite useful as it provides information that I can use to fix the issue. This SSIS Project contains a date parameter BackDate, which I had not set. Here’s the parameter.
I set this parameter to a date between 1/1/1753 and 12/31/9999 and deployed the project again. This time, no error.
I hope that you have found this post helpful, especially if you haven’t deployed a package to SSIS in SQL Server 2012 or later.
Data aficionado et SQL Raconteur