SSIS (Sql Server Integration Services) is a proved technology to load or modify data in or between databases. Anyway, it always worked untill i ran into a weird issue.
Normally, you design your project in Sql Server Data Tools (SSDT) test it, fix the bugs, retest it and loop through this process n+1 times where n is the amount of runs that don’t work.
The basics: SQL Server 2017 CU 16, SSDT latest release.
The job:
It’s reading a table, counting the number of records and writing the result to another table. It’s running under integrated security and my account is sysadmin. I’ve created the SSIS DB and own the thing.
I had to use ADO.NET for the source and target because the OLEDB ran into a missing driver issue.
I’ve saved the package and when i run the package from disk, it works as expected.
There are no exceptional settings or changes that might influence the behaviour of this job. All it should do is pick-up the file and execute the thing.
And, that works as expected:
But, the issues begin to pile up when i want to deploy the project.
When i want to deploy from SSDT:
Ok, let’s check the catalog.operation_messages table on the SSIS database:
It looks like the deployment wizard is unable to reach the database to do any work.
To prevent a load of almost the same screenshots, this happens when i deploy from SSDT, when i import from SSMS (SSISDB -> Folder -> Projects -> Import Packages) and even when i run the ISDeploymentWizard.exe from the commandline.
If anyone has an idea where to from here, i’d be happy to hear it! In the meantime i will keep on investigating this myself. I’ve read there are some issues with the latest version of SSDT but the work-around to use the command-line is failing me as well. I haven’t found an active issue on SSIS Deployment (yet) but maybe my Google-Fu is failing me…
Reitse's blog 