June 6, 2018 at 9:28 pm
i have around 500 million records in one table. I am trying to aggregate data which is before 6 months into another table and delete those records in base table. All columns are with nvarchar(max) in raw table. transformation is failing when there is bad data in some of the columns. how can i send those bad rows some other failure table and aggregate right records and send to aggregate table. Please advise.
June 8, 2018 at 8:01 am
If you use SSIS, rows that do things like violate primary key constraints or cause other insert errors can be redirected to an error output, but then you need a very generic table without any constraints at all to hold that data. Alternatively, if there's a way to specify in T-SQL a specific condition, that testing for doesn't cause an error, but does identify the problem, you could use a cursor, but given the usefulness of SSIS, and the slow-poke nature of cursors compared to set-based code, there's no good reason to pursue that alternative. If you need more details, post back with more details on exactly what you are running into and someone can help you get started down the SSIS road.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 8, 2018 at 2:55 pm
sgmunson - Friday, June 8, 2018 8:01 AMIf you use SSIS, rows that do things like violate primary key constraints or cause other insert errors can be redirected to an error output, but then you need a very generic table without any constraints at all to hold that data. Alternatively, if there's a way to specify in T-SQL a specific condition, that testing for doesn't cause an error, but does identify the problem, you could use a cursor, but given the usefulness of SSIS, and the slow-poke nature of cursors compared to set-based code, there's no good reason to pursue that alternative. If you need more details, post back with more details on exactly what you are running into and someone can help you get started down the SSIS road.
I am trying to do data conversion from nvarchar max to int and have some bad text in some of the rows in the columns which are with int data type. I would like to send that bad rows some error file and skip the main conversion and would like like analyze that data in later time. Please advise the best approach to handle this scenario.
June 9, 2018 at 6:56 am
sgmunson - Friday, June 8, 2018 8:01 AMIf you use SSIS, rows that do things like violate primary key constraints or cause other insert errors can be redirected to an error output, but then you need a very generic table without any constraints at all to hold that data. Alternatively, if there's a way to specify in T-SQL a specific condition, that testing for doesn't cause an error, but does identify the problem, you could use a cursor, but given the usefulness of SSIS, and the slow-poke nature of cursors compared to set-based code, there's no good reason to pursue that alternative. If you need more details, post back with more details on exactly what you are running into and someone can help you get started down the SSIS road.
+1 SSIS will allow you to direct errors to where you would prefer either a table or a text file, with so many rows a table may be preferable.
...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply