How to manipulate data while dts is exporting to sql server from excel

[Kingkumar]

Hi,
I have few excel sheets which i need to export to sql and then run sql queries/commands and output the final result.
But now the problem i am facing is one of the columns in sql excepts no null values but some of the sheets have some rows in that column as null. because of which the dts packages fails so right now i have manually look for those columns and delete the rows having null in that column.

All i want to do is to make it automatic deletion and going to next record in the package itself.
Dont know how to go for it.
Any help or routing to help would be appreciated.
thanks

 

[JayKusch]

If you think the number of records that will fail because of the NULL issue is less than 1000, you could tweak your DTS package to bypass them.

SO ... open your DTS package, click on your "Transform Data Task" that move the data from your worksheet into the table and then choose "Option". Under the "Data Movement" section you can set the "Max Error Count" to 999.

If you have more than that, you could transform those NULLS to a value you can identify later and remove OR you could import the worksheet into another table with the same layout EXCEPT let the field in question accept NULLS. Then export the good records from this table to your original targeted table.

Thanks

J. Kusch

 

[Kingkumar]

how can i transform those nulls to be identified later.
i am totaly new to dts so would appreciate if you could tell meor guide me to a url which tells me how to do this.
thanks.

--king

 

[Catadmin]

When he says transform those Nulls to a value you can identify later, he means "Pick a number, any number" kind of thing.

Basically, pick an alphanumeric value that you know isn't currently being used in that column and isn't likely to be used. Change your nulls to that value, then once the DTS job is done, change that value back to nulls in the new table.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Kingkumar]

Thanks catadmin but when i asked how to tranform what i wanted to know is how can i do it in the dts package itself instead of manually doing it in the excel file.


I know you can write a activex script but was wondering were to do that.


Thanks.

 

[MDXer]

Set a default value on the SQL columns that will be reiciving the null values to something like -99. then when the nulls are to inserted SQL will place -99 in place of the Nulls. You could also probably do this within the DTS task by using an ActiveX transform in you Data pump task and looking for nulls there buth this would come with a possible significant performance hit.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[yksvaan]

Create a ActiveX transformation instead of a Copy Column, and validate data there. There's an example in sql server documentation (BOL) that comes with the product in installation of (the server or just) client tools(Enterprise Manager). If you don't know Books Online, I recommend to find out about that. Anyhow, BOL is also available at Microsoft

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_ax_06er.asp

See the section Validating Data, there's the example how to skip rows.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]