February 18, 2008 at 2:27 pm
Hi,
I have a SSIS package that imports data from a text file into a table, using a Flat File Source and a SQL Server Destination component. Sometimes the package fails because of some unusual date formats in the text file. It fails at SQL Server Destination with the following message:
"Error converting data type DBTYPE_DBTIMESTAMP to datetime."
Is there a way to find out what column generated the error, and maybe the value in that column that caused the error?
February 18, 2008 at 11:08 pm
I hope you don't have too many columns to ease your problem here. Basically, this is one of those famous problems where the flat file would have been generated by Oracle or something similar. If you are not familiar, try looking out for the column where it state somekind of datetime format in the flat file itself. (The easiest way is by just previewing it in the Flat file source you already have. Try looking out for some format like this: 20080218 or 20080218000000. That is the datetime column that is giving you this problem! Good Luck!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 5:14 am
You can capture the errant rows to another file for review.
Edit the Flat File connection and change the Error Output behavior from Fail Component to Redirect row.
Next, drag a Flat File Destination on to the canvas. Connect the red arrow from the source to the destination.
Edit the new destination and define the output file name; the columns should be mapped automatically.
When reviewing the output file for errors you may find the ErrorColumn to be misleading because it doesn't match up to a columns ordinal number or description. However, you can match that up by opening the Advanced Editor of the Flat File source, going to the Input and Output Properties tab. Look for LineageID in each output column.
February 19, 2008 at 3:31 pm
Just to add my 5 cents. The same applies to the destination source errors.
For example you have a table having integer rows, but the flatfile supplies illegal strings.
In this case you connect a flatfile destination to the database destination using the red arrow, and dump the error info into the text file.
The ErrorColumn would be the ID of the input columns found in the Advanced Editor of the database destination on the Imput and Ouptut Properties tab.
In my case it was 100, 99, 98 etc. So it could be easily manually mapped.
Now a question to experts: HOW to automatically retrieve the name of the errorneous column by its ID?
This question was I believe originally asked and also someone else was asking a similar question -- they wanted to log the error table, error column and error value in a generic error table..
Is it possible at all?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 19, 2008 at 3:47 pm
VALEK (2/19/2008)
Just to add my 5 cents. The same applies to the destination source errors.For example you have a table having integer rows, but the flatfile supplies illegal strings.
In this case you connect a flatfile destination to the database destination using the red arrow, and dump the error info into the text file.
The ErrorColumn would be the ID of the input columns found in the Advanced Editor of the database destination on the Imput and Ouptut Properties tab.
In my case it was 100, 99, 98 etc. So it could be easily manually mapped.
Now a question to experts: HOW to automatically retrieve the name of the errorneous column by its ID?
This question was I believe originally asked and also someone else was asking a similar question -- they wanted to log the error table, error column and error value in a generic error table..
Is it possible at all?
That was the function of the red arrow(failure) meaning, if there is a mismatch in datatype, it would take the whole row and put it into the error table. In my case, I had derieved column transformation to further bring in the column using those id's you are talking about.Hope this helps!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 19, 2008 at 5:03 pm
SQL King (2/19/2008)
That was the function of the red arrow(failure) meaning, if there is a mismatch in datatype, it would take the whole row and put it into the error table. In my case, I had derieved column transformation to further bring in the column using those id's you are talking about.Hope this helps!
The question is basically this.
Imagine you have 100 tables with on average 50 rows in each which may fail. Then you would have a generic table which looks like this
error code | table name | column name | error data
--------------------------------------------------
And then you would relly like to log the table name and column name and also the value which made the process fail for further analysis.
Table name can be hardcoded, thats okay, but how do you get the column names?
I dont believe you need to put 5000 if-else or case blocks with column names hard coded in the script!!
Is there a way to programmatically get the column name by its ID within the script?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
February 19, 2008 at 5:12 pm
VALEK (2/19/2008)
SQL King (2/19/2008)
That was the function of the red arrow(failure) meaning, if there is a mismatch in datatype, it would take the whole row and put it into the error table. In my case, I had derieved column transformation to further bring in the column using those id's you are talking about.Hope this helps!
The question is basically this.
Imagine you have 100 tables with on average 50 rows in each which may fail. Then you would have a generic table which looks like this
error code | table name | column name | error data
--------------------------------------------------
And then you would relly like to log the table name and column name and also the value which made the process fail for further analysis.
Table name can be hardcoded, thats okay, but how do you get the column names?
I dont believe you need to put 5000 if-else or case blocks with column names hard coded in the script!!
Is there a way to programmatically get the column name by its ID within the script?
You got good explanation here. Okay, no, you don't plug in 5000 if else. The magic lies in the script component where you store the column in the buffer wrapper and call it from the script inside. You would need .NET to do this.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 20, 2008 at 7:19 pm
Here's one way to include the output column name in the error file.
The overall process is to read in the SSIS package and create a collection of lineageId/Column name pairs. Then that collection is used as a lookup on the ErrorColumn ID to get the Column name.
Step 1: Add a new Connection Manager using the File Connection and point that to the packages .DTSX file; name it mySSISPackage.
Step 2: Add a package variable named myCollection and set the data type to Object.
Step 3: Add a Script Task on the Control Flow canvas and connect that to the Data Flow Task.
' Add Import System.Xml and a reference to System.XML
'
Public Sub Main()
Dim myConn As String
myConn = DirectCast(Dts.Connections("mySSISPackage").AcquireConnection(Dts.Transaction), String)
Dim myXMLdoc As XmlDocument = New XmlDocument
myXMLdoc.Load(myConn)
Dim myCollection As New Collections.Hashtable
Dim nodes As XmlNodeList
nodes = myXMLdoc.SelectNodes("//outputColumn[@lineageId]")
For Each node As XmlNode In nodes
myCollection.Add(cInt(node.Attributes.GetNamedItem("lineageId").Value), _
node.Attributes.GetNamedItem("name").Value.ToString)
Next
Dts.Variables("myCollection").Value = myCollection
Dts.TaskResult = Dts.Results.Success
End Sub
Step 4: Add a Script Component to the Data Flow canvas as a transformation. Place it between the source and the error output. Add two output columns named ErrorDescription and ErrorColName; DataType=string, Length=255.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
Dim myTable As Collections.Hashtable = CType(Me.Variables.myCollection, Collections.Hashtable)
If myTable.ContainsKey(Row.ErrorColumn) Then
Row.ErrorColName = CStr(myTable.Item(Row.ErrorColumn))
Else
Row.ErrorColName = "column name not available"
End If
End Sub
Step 5: In the Error Destination add two new columns and map those to the ErrorColName and ErrorDesc that were created in the previous step.
February 20, 2008 at 8:09 pm
Thankx
This IS and is NOT what I wanted to see.
This is a solution but the solution "through the ar$e".
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
April 23, 2008 at 1:56 pm
Hi,
Please send me one example where error output is directed to some SQL table...
Thanks
Pradeep
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply