SQL Import Wizard data types dont match
-
While importing data from a text file using the wizard i get the error
Invalid character value for cast specification
There's a bad record in the file... but the file is huge so it's nearly impossible to find it by hand. Is there a way to import the data and ignore records that are malformed or the data isn't in the data type it should be? TIA
-
While importing data from a text file using the wizard i get the error
Invalid character value for cast specification
There's a bad record in the file... but the file is huge so it's nearly impossible to find it by hand. Is there a way to import the data and ignore records that are malformed or the data isn't in the data type it should be? TIA
With SQL Server SSIS add a data transformation task and add a datasource (where the bad data is) and a destination (scrubbed data). drag green arrow from source to destination. Add a second destination and set on failure of first destination to redirect to second (red arrow). You can use a flat file if you want.
-
While importing data from a text file using the wizard i get the error
Invalid character value for cast specification
There's a bad record in the file... but the file is huge so it's nearly impossible to find it by hand. Is there a way to import the data and ignore records that are malformed or the data isn't in the data type it should be? TIA
I NEVER use transforms in the ETL layer. I load the data in a table using large varchar fields and use a procedure to do the transforms. This maybe (is) old fashioned but I find I have much better control over the data using a procedure. I can manage the exceptions more to my liking and the bloody thing doesn't break in the middle of the load. One reason I dislike SSIS and hate Biztalk is the complexity they instill into a basically simple operation.
Never underestimate the power of human stupidity RAH