A process that I don't have control over is dropping and re-creating tables in a MySQL database every night. This wouldn't be a problem (I think), if it re-created the tables identically every time. But it doesn't: every morning I come in and open up my SSIS project, and I get new metadata validation errors/warnings in random tables/columns, because this overnight process is creating varchar
columns with a length that varies depending on the data, so yesterday I had a column with a length of 90, and today the same column in the same table now has a length of 208, and tomorrow might be a different story... or not.
This MySQL database is my primary data source - I have my Staging database on SQL Server, and with a linked server connection I'm using SSIS to select data from these tables and into the SQL Server tables (with a few added columns), which I'm truncating before I populate them with the remote data (so the metadata in this Staging database is fixed).
Why does SSIS need to validate external metadata? If I turn off design-time validation, it says it's delayed to runtime - does that mean I would only be pushing the problem from design-time to run-time, and still get a failing package?
I have a T-SQL script that can perform the data transfer from MySQL to the SQL Server staging tables, but that script runs in about 45 minutes while the SSIS package, when all the metadata is up-to-date, runs in about 5 minutes - needless to say I prefer finding a way to get SSIS to stop whining about outdated metadata than to use that script.
Is there a way to work with this shape-shifter of a data source in SSIS and keep my sanity?
CAST(LEFT(Field,500) AS VARCHAR(500))
in your view on SQL Server to lock the metadata, and then compare your table metadata in the SSIS package by comparingINFORMATION_SCHEMA.COLUMNS
from SQL Server and MySQL for your local view and the remote table to detect truncation?CAST(Field AS VARCHAR(max))
. Your performance will suffer, but you don't have a lot of choice given your requirements.