Checking schema of source table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Checking schema of source table

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-09-23 : 03:53:43

Greetings!!

I have a MsAccess db containing a table called Employees which i am transforming to Sql server 2005. Everything is working fine. I am using Foreach File enumerator and uploading the files one by one.

However I now plan to validate the schema of MsAccess before uploading it. For eg: My employee table in msaccess is as follows :

Employees
empId int,
empName varchar(60),
empAge int

Since the files come from different vendor, while looping, i want to perform a check if the empid or empAge are not of type long or are not null. If they are of type smallint,i have no problem.

However if they are larger datatypes than the the ones kept in Sql server, then the file needs to be logged in the db with the reason and moved to the error folder. In short, if the datatypes in access tables are smaller than those in Sqlserver, allow it, otherwise reject it.

THe schema of Sqlserver table is same as of that of Employees in msaccess.

How do I do it.

Thanks ,
Lolsron

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 05:06:18

I would tackle this rather differently, but you may be a bit far down the road to change horses!

"I am using Foreach File enumerator and uploading the files one by one."

I would use DTS to import the tables to StagingTables. I would probably use a different databases to the actual database you are going to use, so that the StagingTables don't "pollute" the application database!

If the StagingTables have the same datatype as the original Access data then the data won't be any less correct.

So an INT in Access, and an INT in SQL Server can't, for example, hold a text string.

However, an INT in Access and a TINYINT in SQL Server could have a problem, of course.

So in those instances I would set the StagingTable's Column Datatype to something loose - even varchar(8000) if necessary to ensure that nothing gets chopped off, or changed by some implicit conversion.

I would then add two additional columns:

ErrorNumber [int] and ErrorMessage [varchar(8000)]

Then I would do some UPDATEs on the StagingTable like:


UPDATE MyStagingTable
SET ErrorNumber = 1,
    ErrorMessage = COALESCE(ErrorMessage+', ', '') + 'empId NULL'
WHERE empId IS NULL

UPDATE MyStagingTable
SET ErrorNumber = 2,
    ErrorMessage = COALESCE(ErrorMessage+', ', '') + 'empId too large'
WHERE empId > 32000

UPDATE MyStagingTable
SET ErrorNumber = 3,
    ErrorMessage = COALESCE(ErrorMessage+', ', '') + 'empAge is NULL'
WHERE empAge IS NULL

UPDATE MyStagingTable
SET ErrorNumber = 4,
    ErrorMessage = COALESCE(ErrorMessage+', ', '') + 'empAge invalid'
WHERE empAge < 0 OR empAge > 115

then you can list any errors with:


SELECT *
FROM MyStagingTable
WHERE ErrorNumber IS NOT NULL

Once you are happy that the data is "clean" you can then do:


INSERT INTO ProductionDatabase.dbo.MyRealTable
SELECT *
FROM StagingDatabase.dbo.MyStagingTable

Of course your SQL Server database should have a CHECK CONSTRAINT for both empID and empAge so that no rubbish data [;]) can creep into the table in the future

This steps can be repeated easily, so you can carry on using your Access database right up until the time to switch over, then you run all the steps again to migrate and check the data, and if its OK you then turn off the Access database - and keep your fingers crossed!

Kristen

lols
Posting Yak Master

174 Posts

Posted - 2007-09-23 : 05:21:59

Hi Kirsten,

Thanks for your time. I am using a staging table and my Access and Sql Staging table have the same schema. However the prob is that at times vendors will give files that do not adhere to the schema. At such points, i just want to ignore those files and dump them in the error folder and keep a track of the records dumped in a sql server error table to know which file had the error.

I want to compare the schema of the incoming access tbl fields with my desired schema and all mdb's having data types that are higher or incompatible with the desired schema should be moved to the error.

thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-23 : 05:53:13

If you want to automate this just based on the Datatypes of the SQL Database then that's probably quite a lot of work.

One way would be to rely on the Check Constraints on the SQL Server table:


BEGIN TRANSACTION
INSERT INTO MyRealTable
SELECT *
FROM MyStagingTable
IF @@ERROR <> 0
BEGIN
    ROLLBACK
    ... Mark the batch as Failed ...
    ... Rename original source file ...
    ... Record errors in Error Table ....
END
ELSE
BEGIN
    COMMIT
END

but I think you will need to put individual tests in place field-by-field, as I described above, to get any meaningful messages for the end user.

One of my gripes about SQL Server is that it doesn't give you much of a clue as to which record(s) has failed a bulk INSERT, such as the one above.

Kristen

lols
Posting Yak Master

174 Posts

Posted - 2007-09-23 : 06:39:59

Hi,

Thanks again for the reply. So do I pull up a Execute Sql Task and then write the code over there ? How will i pass the file name that is coming from a ForEach loop container(coded in a variable called FilNm) to the sql query? And then if the results are ok, execute the Data Flow task that transfers rows from access to sql and it the results are not ok, fail it. Correct?

How will the flow of my transformation look like?

thanks.

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 02:19:05

You could probably use DTS.

"How will i pass the file name that is coming from a ForEach loop container(coded in a variable called FilNm) to the sql query?"

Assuming that DTS won't just do this for you, you can:

call a stored procedure and get it to bulk import the file for you (i.e. pass the filename)

put the file in a given folder and have a scheduled task process all files in that folder

Use a Web Service to pull the file in - probably need to convert the file to XML first

and probably a number of other more esoteric routes too!

But if your source data is in Access you can just query it directly in SQL - and JOIN it to the tables in your SQL Database, or insert into staging tables and the further-process.

You want to be doing data import SET BASED, rather than row-by-row.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources