3

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?

  • 2
    What if you use a 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 comparing INFORMATION_SCHEMA.COLUMNS from SQL Server and MySQL for your local view and the remote table to detect truncation? – Bacon Bits Jan 22 '15 at 17:08
  • @BaconBits that looks like a good idea. I'd have to deal with truncation in the Staging-to-NDS ETL though. – Mathieu Guindon Jan 22 '15 at 17:12
  • 1
    You should be able pick reasonable sizes for the fields. Metadata scanning should only throw an error very rarely. Your data is only going to vary so much or you wouldn't use varchar in the first place. MySQL can't store War and Peace in a single varchar field. It's limited to 65535 bytes per record unless they're using blobs, but blobs wouldn't be as much of a SSIS problem. If it's really that big of a problem that you can't ever fail or you can't ever wait to fix it, use CAST(Field AS VARCHAR(max)). Your performance will suffer, but you don't have a lot of choice given your requirements. – Bacon Bits Jan 22 '15 at 17:30
1

In that unpleasant case, I think the best strategy is to compartmentalize it away from SSIS. Either move that out to a linked server with an altering view or use some other etl tool like a powershell script to get it out of the dancing schema into a table with a fixed one. If you have any history you should be able to guess a reasonable value for the upper size of the field. You could always make it varchar(max). I'm presuming MySql is doing a 'select into' to create the table. In any case MySql is not making any reliable assertions about the data, so you need to do that yourself before SSIS is going to be happy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.