question

TonyClifton avatar image
TonyClifton asked

Force Excel import to fail when there is text in numeric column

Hello, I import excel files via SSIS to a sql server table. In order to get numeric values for a column that sometimes can have no entries or blank cells, I have set 8 template rows at the top with 0,00 to import this as type money. What I noticed is, that text entered somewhere in this column will be replaced with NULL in the DB. But instead of NULL I would like the import to fail. To get the user to correct it. I also have calculation formulas for that column that can produce blank cells but these I don't mind being imported as NULL (which it does right now). Is there a setting in SQL Server or somewhere else that would help me get this job done or is this a tricky task? thank you.
sql-server-2008ssisexcel
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
Is it an option for you to create a column constraint (NOT NULL)? So the import will fail in this case...
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TonyClifton avatar image
TonyClifton answered
no, I can set a NOT NULL constraint for the column. I guess I'll just import everything as varchar to a temp table and do my cast there.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.