Thanks dgosbell. That pointed me in the right direction to solve the problem.
When I ran your suggested BIML, I received the same error.
But when I changed the SQL statement to retrieve only one field (policy_id_i), the error went away. In its place, I received warnings about 4 fields being "out of synchronization with the database column. The latest column has new precision: 0 new scale:
0. Use advanced editor to refresh vailable desintation columns if needed". If I tried to change the select clause in the SQL to include any of those 4 fields, the error returned.
I didn't really believe the warning, because I was seeing the warning immediately after generating the package, and I knew the database schema wasn't changing that fast. I tried to figure out what was the same about these 4 fields, and came up empty. They were
all different data types (float, datetime, timestamp).
Then I tried changing my connection/source/destination from AdoNet to OleDb, and when I did that I only got 1 warning instead of 4. And the one field that remained had a SQL TIMESTAMP datatype. Realizing that it doesn't make sense to migrate a TIMESTAMP value
between databases, I removed that field from my source and destination and everything works fine now.
Here is my final working BIML:
<Connection Name="EDW_Stage" ConnectionString="Data Source=SQLDWTEST;Initial Catalog=EDW_Stage;Integrated Security=SSPI;Provider=SQLNCLI11;"/>
<Connection Name="Product" ConnectionString="Data Source=SQLTEST;Initial Catalog=Product;Integrated Security=SSPI;Provider=SQLNCLI11;" />
<Package Name="TestBimlPackage" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" SsisPackageType="5" VersionBuild="85" CreatorName="HAI\bhurley" CreatorComputerName="ITCONSULTANT630" CreationDate="2014-11-07T12:50:51">
<Dataflow Name="Data Flow Task">
<OleDbSource Name="Source" ConnectionName="Product">
<DirectInput>select * from "DW"."policy_changes_data"</DirectInput>
<OleDbDestination Name="Target" ConnectionName="EDW_Stage" >