SSIS OnError SQL Task won''t insert.
-
Hi gang,
I've been trying to improve error logging on a text file import.
I added SQL Tasks to the main package and the "executible" that was erroring.
I have tried using an expression and parameterized SLQ expression.
When I run the task by itself it properly inserts null values, the insert works.
When I run the package:
with the expression it does not evaluate,
with the parameterized SQL it does nothing.
How do you get this work, why would it either evaluate or insert when it's just the task, but error or do nothing when the package is run?
thanks y'all!
----------- parameterized --------------------------
INSERT
INTO [Inside].[dbo].[ETL_Batch_Error]
([comment]
,[errorDatetime]
,[errorNumber]
,[errorText] )
VALUES(
'BKS_Store_Data_File_ETL'
,GETDATE()
,?
,?
)
System
::ErrorCode LONG 0
System
::ErrorDescription VARCHAR 1
---------------- expression --------------------------
"
DECLARE @BatchRunID int
SELECT @BatchRunID = MAX(ID) FROM dbo.ETL_Batch_Run
INSERT INTO [Inside].[dbo].[ETL_Batch_Error]
([batchRunID]
,[comment]
,[errorDatetime]
,[errorNumber]
,[errorText] )
VALUES(
@BatchRunID
,'BKS_Store_Data_File_ETL'
,GETDATE()
,"
+ (DT_STR, 5, 1252)@[System::ErrorCode] + "
,'"
+ (DT_STR, 1000, 1252)@[System::ErrorDescription] + "'
)
"
Skål - jh
-
Here's the latest expression. It will now evaluate at run time but...
As before it works when executing the task by itself and it has a null error, but when running the entire package this onError sql task is hit but nothing is inserted in the log table, yet the OnError task itself does not generate an error it turns green and everything. The progress tab seems to indicate the statment is 100 % successfull three times.
thanks gang
-------------------------------------------------------
"
DECLARE @ErrorNumber float
DECLARE @ErrorDescription varchar(1000)
SET @ErrorDescription = '" + REPLACE(@[System::ErrorDescription] , "'", "" ) + "'
SET @ErrorNumber = " + (DT_STR, 250, 1252)@[System::ErrorCode] + "
INSERT INTO [Inside].[dbo].[ETL_Batch_Error]
([comment]
,[errorDatetime]
,[errorNumber]
,[errorText] )
VALUES('BKS_Store_Data_File_ETL'
,GETDATE()
,@ErrorNumber
,@ErrorDescription )
"
Skål - jh
-
Omhage,
I'm getting SSIS burnout so this is my next to last post of the day.
Based on what you've described so far, I'm going to leverage my experience so far with SSIS to give you 2 recommendations.
1) It sounds like you are trying to use SSIS's natural error handling features, but you want to decode the seemingly useless integer values that it returns for Error Column and Error Description.
Since SSIS is built on the .Net framework, one can now take advantage .Net's powerful objects and properties for SQL Server related tasks. In addition, as part of .Net 2005's release, MS included several new objects and props. One of the most useful is the ComponentMetaData property. That tool contains the text value of the error code returned by SSIS, and you can capture that data by using the Script Component Data Flow Transformation.
Since I'm not that smart, I relied on the MSDN documentation to guide me through this process. Here's the link: http://msdn2.microsoft.com/en-us/library/ms345163.aspx
This is a very well-written article (I'd give a 9.5 - 10), and I'm sure that you'll be able to implement it without much hassle.
The only bad thing about the ComponentMetaData property is that you can not decode the Error Column (and I haven't met a SSIS developer yet that isn't a bit perplexed and ticked off by this fact). So value of the column that error'd out will remain an integer, but you can capture the text/problem in the column that is caused the error so that kinda of makes up for that oversight.
2) My 2nd recommendation is that it sounds like you may also want to include some dynamically generated values in your log table so that you can better troubleshoot/monitor the package's results.
For ex., I have a package that needs to pull in data from several different customers' flat files and insert that information into a table that will contain the resulting co-mingled data.
So if one of the flat files has a row that errors out, I'll need to know exactly which customer's file caused the problem. Unfortunately, the customer's name is not part of the flat file so I have to add that information at run-time.
The best way that I've found so far to do that is to generate a package variable. Populate that package variable (hard code it within SSIS, populate it at run time via a Management Studio Job or at the CMD prompt, or populate it dynamically using the SSIS Execute Sql Task), and then use the Derived Column Data Flow Transformation (DCDFT) to insert that data as a value in your recordset.
Unfortunately, SSIS is not very intuitive especially when it comes to how it named certain tasks, but I'm finding that the Derived Column transformation is one of SSIS's most useful features.
Basically, think of this task as a way of adding any type of new information to your data source recordset. For example, I have a variable of varCustomer that contains the name of customer whose file I'm executing.
In the DCDFT editor window, I use the expression column to store the value of the variable (i.e., @[User::varCustomer]) and then if I want this to be a new column, I make sure that I type a column name for it in the Derived Column Name field, and I make sure that the Derived Column field contains the value "<add as a new column>". Also confirm that you new column's data type matches the data type of your variable, then click OK to exit the editor window.
Now link the DCDFT to your destination. Double-click on your destination to edit it, and go to the Mappings property. You should see your new column in the Input Column window and you can subsequently map that value to its corresponding destination column. If you used the Script Component task, you'll also see these new columns as well.
Now, you'll have a destination table that contains the error description in English instead of an integer value, and you'll also have whatever dynamically populated values that you wanted to include.
HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply