Create DTS package validation

[webdev2]

Hi,

I have created a package that transfers transfers data from MS Excel file to the SQL Server 2000 database. I would like to add some data validation before the data is pumped into the database. How can I do that?

I would like to check the name of the file, column names (are there are spaces in column names), columns data types, the size of the file?

Thanks.

[iburyak]

1. Load data into work table first.
2. Create stored procedure that does data validation and even can enter infected records with comments into error table or something like this.

Your creativity is a limit here.

After initial load into a work table just create another step that executes stored procedure that does actual insert into a production table.

Good luck.

[webdev2]

The DTS package will be executed through the ASP application and I would like to validate the data before it enters the database. Could you show some code or example how to do it. So far I entered the validation to check if the file is there and file name by creating ActiveX script task on first connection (Excel 97-2000) in the package.

function main()
sFile = DTSGlobalVariables("gFileName").Value
Set oFSO = CreateObject("Scripting.FileSystemObject")

if oFSO.FileExists(sFile) then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if

end function

Can you help with column names and data types validation?

[iburyak]

Did you create a DTS package that transforms data?
Make destination table a work table (you should create work table on a server side) not an original production table.

[webdev2]

No Use From You!

I will manipulate the data once in my work table to enter it properly to production table. But, some of the data is not transfered to my work table since some rows in Excel contain numbers and letters like 505X and the record is empty in my work table.

Can you give some code examples or not?

[iburyak]

I don't like what you said in your first sentence.
You should understand that I am not a salary paid adviser here. In some cases I don't know how to help you because I don't see what you do. If you said that you created a DTS package I assume you know how to do at least that.

My suggestion, in case where not all rows get to a table, is to create a work table with no datatypes just varchar columns. Make them big enough to fit all data.
After that you would be able to load all records and sort out the once that for example can't be converted into int.

To sort out alphanumeric values do following:

[PHP]Select * from work_table where column like ‘%[a-z,A-Z]%’

Or

Select * from work_table where column like ‘%[^0,1,2,3,4,5,6,7,8,9]%’[/PHP]