I have an SSIS package that has a data flow task that runs fine when I run the package locally in SSDT 2017 but when I deploy the package to the SSISDB catalog and run it, I am getting data conversion errors.
The data flow task consists of a flat file source and an OLEDB destination.
I can see that the offending column(s) are instances where a DT_STR field from the flat file is trying to load into a DATE field in a SQL Server 2016 table source.
This is probably just matter of me going in and fixing the data types as needed so they match but between source and destination (unless DT_STR can flow ok into SQL Date field in which case no chang would be necessary).
The thing I can't wrap my head around is why the package runs fine and loads data from the file to the table without any issue when I run the package locally but when I run it on the server I deploy it to, I get the following conversion errors.
Only thing I could think of is if the version on server wasn't the same as the version I am running locally but I have already ruled that out by adding an insert statement that would only exist in the version I am deploying to server and I see it does the insert so I know I am running the same package.
From the error message I am guessing the package is failing when it does it's validation and not while in the process of importing any of the records.
Has anyone ran into anything similar to this or have any suggestions of further steps I can take to troubleshoot. As a lot resort I'll probably just delete and recreate the data flow task.