October 31, 2011 at 10:18 am
Hello All,
I am moving a few tables from MS Access 2007 to MS SQL Server 2008. Even though the table names and column names match exactly, there are a few changes in the datatypes. For e.g. Yes/No data types in Access have been created as smallint datatypes in SQL. This was done to retain values as is (Access stores -1 for True and 0 for False. If it is converted to a BIT, the corresponding values would become 1 and 0, respectively. This will cause issues at a lot of places in the application, so it was decided to retain the values as is).
Now, here is my question. I am using SSIS to migrate this data. Once the data has been moved, I want to run some validation queries to identify any issues during data migration. The simplest one would be...
SELECT src.ID, dest.ID, src.FirstName, dest.FirstName, src.LastName, dest.LastName,
src.DOB, dest.DOB, src.SSN, dest.SSN
FROM SourceTableInAccess src
INNER JOIN DestinationTableInSQL dest ON src.ID = dest.ID
WHERE src.FistName <> dest.FirstName OR src.LastName <> dest.LastName OR
src.DOB <> dest.DOB OR src.SSN <> dest.SSN
But, the above query assumes that ID column has been loaded correctly.
In a nutshell, I am looking for a query that will find out any disparities between 2 tables (mismatching column data and/or mismatching rows).
Is there a feature/control in SSIS that can be used for this?
Your help on this topic will be grately appreciated.
Regards
October 31, 2011 at 2:22 pm
To my knowledge there is not a transform in SSIS that would do this sort of field by field comparison natively, easily. You could use a Conditional Split comparing every column in both tables much as you do in your sample SQL.
In T-SQL, since I expect that each comparison would be a one time deal, I would probably do something like this to save typing.
SELECT *
FROM DataSourceTable
EXCEPT
SELECT *
FROM DataDestinationTable
This will show every row in the DataSourceTable that does not have an exact match in the destination. You might also want to reverse the order to see if anything snuck into the DataDestinationTable that shouldn't have.
November 2, 2011 at 2:57 pm
Thanks, Daniel.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply