January 7, 2009 at 1:49 pm
hey everyone,
Have an interesting scenario that we've been tackling here for the last few days.
We have a nightly SSIS package that copies data from a staging table into a final "history" table where the data resides for repotring purposes.
We have a Data Flow task setup to do the Inserts. It had been running smoothly for all of 2008. Come January 1st, 1 of the columns in the destination table has had its data corrupted, ie mysteriously transformed. The source and destination fields are decimal(12,2). After running the Data Flow task the destination field has numbers in it that technically too big to even be in the field, the values are all decimal(14,2) and higher. The only way to see this bad data is to CAST the bad field as a larger one when doign a SELECT from it. I can confirm the data in the source table is not corrupt and has proper values.
The Data Flow task uses a simple SELECT as its source and the destination is using Fast Load. Source and Destination are in the same SQL Server 2005 DB, using OLE DB Source and OLE DB Destination.
So I've performed some tests, here are my results:
* If I perform a manual INSERT with an SQL statement, the values appear fine in the destination table.
* If I turn Fast Load off in the Data Flow Task the values will be correct, although the task takes much longer
* If I create a new "test" table, exactly like the Destination table with the issues - same filegroup, partition scheme, indexes etc, the data will load fine using Fast Load.
* I tried deleting and re-creating the current Data Flow task but the bad data still occurs.
So it appears Fast Load is not liking something about my Destination table, and really only 1 field in that table. It also appears that something just isn't right with the Destination table as I can pump the data into a duplicate table with no issues.
This is where I become stuck in not having come across this type of issue before. Sure I could leave the pump as a "non-fast-load" one for the time being but it appears there is a problem with this table that may surface down the road.
This SSIS package has other very similar data flow tasks being performed with other pairs of tables with no data issues.
thanks for your time.
Jason
hmmmm beer.
January 7, 2009 at 1:53 pm
Have you considered on deleting and re-creating the package?
January 7, 2009 at 1:57 pm
That option is certainly being discussed. The SSIS package is quite involved besides this step and would take time to re-create. If our research points to trying that after exhausting other options, it will certainly be done. I can leave the current Data Flow task as a normal non-Fast-Load pump and it will work, just much slower. So that buys me the time to investigate, re-create etc...
hmmmm beer.
January 7, 2009 at 2:31 pm
instead of recreating the whole package just recreate that one data flow and see if it works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply