February 7, 2008 at 2:27 pm
All,
Maybe someone out there can help me out.Let me be frank I have not written many procedures or have much knowledge of stored procedures or SSIS, so do explain to me in detail.Thank You in advance.
My prob is as below.
Daily I have a flat file data being loaded into Table A.
Table A has the following structures
Column1 int, Column 2 varchar(200) , Column 3 varchar(4),Date(datetime).
All was well, till the point the flat file source data type had some problem on the sender side.Therefore I have to figure out a way to validate each data type coming in as well as the length before loading the record to Table A[if all data matches].If there is a mismatch in any one of the data type, the entire row would be moved to an ERROR Table.
The ERROR table schema is as following.
Error Code int, Error Desc varchar(50),Column1 sql_variant,Column 2 sql_variant , Column 3 sql_variant,Date sql_variant.
Error Code would return value as [ 1 for Column1, 2 for Column 2, 3 column3 and 4 for column4] [ referring to the column with mismatch]
[font="Arial Black"]Question 1) What if more than 1 column has data type mismatch, what is the best way to solve this [ as the error code is required][/font]
Error Desc would return the explanation on the mismatch , example 'Non Numeric' , 'Data is more than 200 char '.
[font="Arial Black"]Question 2)How can I include this ?[/font]
Sql_Variant data type is used, so mismatch data that is coming in would be returned as it is.
[font="Arial Black"]Question 3) I have created a proc as attached below, It works fine while I check the mismatch for Column1 , but im not sure on the correct and best way to check all other columns.As my original table has at least 50 columns of different data types.[/font]
[font="Arial Black"]Question 4) If there is an easier way for the accomplish this,Do let me know.[/font]
[font="Arial Black"]Question 5) Once the procedure is created, Which transformation I can use to call as part of my SSIS package that is already there.I tried including SQL Task, saved my proc as a file and called it within SQL Task but it failed.[/font]
Do help me out as it's been 2 days now and im still not getting anywhere.:crying:
FYI:- Attached is my proc currently working for Column 1,Thank You very much.
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 8, 2008 at 7:12 am
To my thinking, if there is a mismatch of data type coming in from input table.The SSIS package would fail.Therefore I would need to just find a way to move the row that has mismatch to ERROR table without stopping the package due to failure.
1) Can anyone guide me through on the settings I need to make on my package, to handle mismatch and move rows to ERROR table.
2)How do I update the Error Desc & Error Code column then ?
Pls guys ..guide me through..Im looooooooosssstttttt
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
February 8, 2008 at 8:24 am
Ok I did get some work done.From flat file connection manager > output 1[Table A] > output 2[Scrpit Component] based on http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx.From Scrpt component > to ERROR table.Now when there is a mismatch in data coming in.Entire row is moved to ERROR table, as below
Error Output = Entire Row from flat file
Error Code = SSIS Generated
Error Column = SSIS Generated
Error Description = Descrption of error.
I need to format the output of my Error Table as such
Error_code|Error Descrption|Row_Num|Primary_key ~ Value| |Column_name|datatype|value|.
Can anyone let me know how I can Insert Row Number / Primary Key /
Column Name/ Data Type and Value in ERROR table?
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply