ODBC, DSNs, SSIS Code Pages, metadata and BIML

The scenario:

GIVEN a SQL 2000 data source
AND a SQL 2016 destination
AND a metadata driven, BIML generated SSIS package to move data from source to destination
WHEN you try build the SSIS package using an OLEDB Connection
THEN SSIS says it can't connect because SQL 2000 is not supported

So, what do you do? Well obviously ODBC comes to mind and so you try that avenue (ala ODBC Driver for SQL Server) only to find you're presented with another unfriendly message:

Now what? Well, simply use the SQL Server provider and that will work. SSIS is able to use it with an ODBC connection, so all is good, until you execute your package and get the most excellent of exceptions, the dreaded VS_NEEDSNEWMETADATA! Or, in my words "AAAAHHHHGGGRRRRHHH!!!" What just happened? My package built without any issues and didn't throw any warnings so why does this happen when I run it? Weird ODBC behavior I guess.

Turns out that the ODBC connection defaults to UNICODE (whereas the OLEDB defaults to ANSI). Now in the yesterday world of hand-cranking your SSIS packages you would just set the BindCharColumnAs property to ANSI and everything would just work. BIML doesn't give you the option to set this so you need to find another way to use the connection if you want to automate the generation of the package.

Linked Servers my friend! Yes, by simply setting up a linked server from the target to the source SQL Server instance, we are able to access the source table using the linked server reference and need only to make a slight tweak to the BIML. Job's a good'n!

Later dudes!