Best Practices: Recording Error Information (Beyond ErrorCode and ErrorColumn)

I'm sorry for asking this, as I'm sure tha the answer is in one of the 208 matches I found searching on "ErrorCode". Unfortunately, this project is overdue and I need a solution fairly soon.

I should add that I've only been developing in SSIS since August 2007.

I have a complicated package, loading about 17 outputs of the XML Source into staging tables. I have been using the error outputs of any standard components I use, out of faith that, if Microsoft provided them, then they should be useful for something. I've been directing all of the error outputs for one of the 17 "tables" from the XML Source into a Union All, and then into an "Error staging table", for each of the 17 outputs. This table includes all of the possible columns, but permits nulls for all of them. It also incldues the ErrorCode and ErrorColumn.

Unfortunately, if these latter two are useful for anyting, I haven't found it yet.

Right now, working with our first "mostly real" data, I'm getting 100% of my input rows written into error tables. Unfortunately, the information in the tables are of limited use in determining what went wrong. For instance, the ErrorColumn seems only to be populated if there was a specific error with a specific column. The Lookup component, doesn't seem to populate ErrorColumn, even if only one column was used for the lookup! No information about the component producing the error output is supplied in the error output, either, which makes it difficult to determine which of the five or so possible error outputs is the one that produced the particular error row.

This proves that I'm missing something simple. How do people handle errors? In my custom components, I learned to use the Fire* methods to produce detailed messages before redirecting the row to the error output, but this sort of thing is not available through the standard components.

Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?

Sorry for the attitude, but just when I think I'm winning, SSIS brings me back down to earth!

If you want to identify which component produced an error then simply hook a derived column component up to the error path that adds the name of the component producing the error to the data path. Thats that one taken care of!

"Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?"

Whats the problem with doing that? Granted you could argue that this information should be included anyway but the rationale for it NOT being there is that it could potentially take up A LOT of memory - a rationale that I agree with. hence you have the opportunity to do it yourself.

Note that if youre using a UNION ALL to collate all of your error rows into one place then you only need to do this once.

-Jamie

|||

Jamie Thomson wrote:

If you want to identify which component produced an error then simply hook a derived column component up to the error path that adds the name of the component producing the error to the data path. Thats that one taken care of!

"Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?"

Whats the problem with doing that?

The main problem is that I don't know how!

The second problem is that there are about 50 or so places I'd have to add one of these components.

In particular, how can a downstream component learn about the component next-upstream? I also don't know how I'd "do it only once" downstream of the Union All.

|||

I've implemented a Derived Column transform for the first few of my error outputs, and it works well enough.

My next issue is that I have two custom components that are very heavily used. They produce good error messages through the Fire* methods. I now would like to take the messages they produce and place them into my new ErrorMessage column. I added a dummy Derived Column transform to the start of the chain of transforms in order to make the ErrorMesage column available with the correct data type and size throughout the transform chain.

The problem is that both of my components do nonstandard things with their collection of input columns, so I can't just add ErrorMessage as an input column. I know how to get the buffer index for an input column and how to use it to get values from the buffer for the column, and how to set them into the buffer.

My question is: can I get a buffer index for a column not in my input columns collection and use that index to set the value of that column? I see that there's a Virtual Input and Virtual Input Column Collection, but I haven't seen examples of how to use it.

This does sound like a hack, and I promise to fix it once I get the leisure to write a UI for these components so they don't have to do strange things with their input column collections.

Thanks.|||

John Saunders wrote:

Jamie Thomson wrote:

If you want to identify which component produced an error then simply hook a derived column component up to the error path that adds the name of the component producing the error to the data path. Thats that one taken care of!

"Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?"

Whats the problem with doing that?

The main problem is that I don't know how!

Oh right. Sorry. Go here:

SSIS Nugget: Get error descriptions

(http://blogs.conchango.com/jamiethomson/archive/2005/08/08/SSIS-Nugget_3A00_-Get-error-descriptions.aspx)

John Saunders wrote:

The second problem is that there are about 50 or so places I'd have to add one of these components.

I'm not understanding why. If you push all the error outputs into a UNION ALL then you just need to add the code that I linked to above in a single component downstream of the UNION ALL. Unless I'm misunderstanding.

John Saunders wrote:

In particular, how can a downstream component learn about the component next-upstream?

It can't but I don't know why you'd want to.

-Jamie

|||

John Saunders wrote:

My question is: can I get a buffer index for a column not in my input columns collection and use that index to set the value of that column? I see that there's a Virtual Input and Virtual Input Column Collection, but I haven't seen examples of how to use it.

Spot on. Yes, you can do that. To be honest, the virtual input is what you see in the designer. The inputitself contais the columns that are selected at design-time. hence the columns in the input are a subset of teh volumns in the virtual input.

This code will return the virtual input and let you process the columns

Code Snippet

IDtsVirtualInput90 vInput = ComponentMetadata.InputCollection[0].GetVirtualInput();

foreach (IDtsVirtualInputColumn90 in vInput)

{

///blah blah blah

}

[that's done from memory so don't hang me if it doesn't work ]

By the way, I will be publishing some code, via my blog and codeplex, in the next couple of days that does exactly this i.e. process data based on the virtual input. Keep a look out for that. Addrss is below.

-Jamie

Best practices for Unicode column supporting mixed languages and searching

(I posted this to sqlserver.server newsgroup but did not get a helpful
response)
Globalization gurus,
The problem is storing in a single column character data from mixed
languages and then providing a search capability to find the best match
given a search string in some arbitrary language. The column, of course, is
Unicode data type with some collation. What is recommendation for table
design for efficiency?
As an example, consider an international directory of business names and a
function to search for a name "like" <some string> where <some string> is
user input in any language. Since collation determines comparison rules it
seems the appropriate collation is one that best matches the language of the
search string. Further, to facilitate matching it seems appropriate to relax
restrictions such as case and accent sensitivity. That is a strict binary
comparison is not "user friendly." However there are performance
implications when the collation of the search string does not match the
collation of the database column.
Has anyone solved this problem or know of a good discussion?
Thank you,
SethSeth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>|||If anyone has ideas on non-FTS solutions I would appreciate that as well.
John,
Good idea!
I have read about FTS enhancements in SQL Server 2005 but I admit to having
an aversion to FTS and dismissed it as an option prematurely. It is
definitely worth consideration.
Seth
--
"John Kane" wrote in message news:OVefrPe2FHA.1140@.tk2msftngp13.phx.gbl...
Seth,
Hmm... This seem a very good fit for SQL Server 2005 Full-Text Search (FTS).
Note, that I'm NOT recommending SQL Server 2000 FTS for this as there have
been specific SQL FTS enhancements for these requirements that are not in
SQL 2000. Specifically, in SQL Server 2005, you can define multiple
languages in one Unicode column and then do CONTAINS or FREETEXT and specify
the language (via LCID) on a per query basis, for example:
select * from table where
contains(*,'formsof(inflectional,"englishword")',language 1033) OR
contains(*,'formsof(inflectional,"chineseword")',language 2052)
The above query will find the englishword or chineseword and inflectional
variations from the same FT-enable column. You can also use this query with
CONTAINSTABLE or FREETEXTTABLE and use the RANK value. As for table design,
that depend upon what you're storing and what combined column you want
returned to your searchers.
You can also control the accent sensitivity via
"CREATE FULLTEXT CATALOG <FT_Catalog_Name> WITH ACCENT_SENSITIVITY = [OFF |
ON]
However, FTS is not case sensitive, but based upon research in to how people
search, case sensitivity is usually viewed as a hindrance as most internet
search engines are case-insensitive as well. Why do you need this level of
control?
Feel free to email me directly if you'd like to continue this discuss in
more details.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Seth" <seth@.no*spam.com> wrote in message
news:Orp0SUb2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> (I posted this to sqlserver.server newsgroup but did not get a helpful
> response)
> Globalization gurus,
> The problem is storing in a single column character data from mixed
> languages and then providing a search capability to find the best match
> given a search string in some arbitrary language. The column, of course,
> is
> Unicode data type with some collation. What is recommendation for table
> design for efficiency?
> As an example, consider an international directory of business names and a
> function to search for a name "like" <some string> where <some string> is
> user input in any language. Since collation determines comparison rules it
> seems the appropriate collation is one that best matches the language of
> the
> search string. Further, to facilitate matching it seems appropriate to
> relax
> restrictions such as case and accent sensitivity. That is a strict binary
> comparison is not "user friendly." However there are performance
> implications when the collation of the search string does not match the
> collation of the database column.
> Has anyone solved this problem or know of a good discussion?
> Thank you,
> Seth
>
>

Best practice question

I'm very sorry if this has been covered before but I can't seem to find and answer with the way I've been searching.

The question is what would be concidered best practice when I have a row of data that I need to add or update but I don't know if the row exists yet. Would it be best to call SELECT for the record and INSERT if no data is found and UPDATE if the record is found, or to attempt the INSERT and then attempt an UPDATE if the insert fails?

The reference to a discussion over the past two days was not relevant. I suppose that I would first try the update; if the record is not present try the insert. In general my preference is to have the application intelligent enough to know what it is doing and have the stored procedure fulfill that specific function -- update, insert, select, delete, list or whatever.

I would urge you to avoid having the DELETE function be in the same stored procedure as the others.

|||I think you have to link this question with your real case. Let me explain: suppose you have to pay a bill, the person that get your money have to find your name or ID to see if you paid or not before. So , i think the SELECT preceed INSERT or UPDATE because this process have to be done by a person.
Another problem is that an computer operator insert a wrong or duplicate product, for that exist triggers, constraints...|||

Code Snippet

IF EXISTS (SELECT Statement)

BEGIN

-- It is there to be modified

END

ELSE

BEGIN

-- It is not there to be modified, can be added

END

|||

OK the way it will probably work is I have a table on another system that will have columns that hold total information for each hour of the day. The idea is to keep the two systems as close to concurrent as possible and so I'll be periodically using ODBC to update the SQL Server table on another system. Most often, the record keyed on a type, name, and date will be there so I guess trying the update first will succeed more often that fail so I was thinking of doing that first but then was wondering if it is more acceptable to first try to insert a new record and change to an update if that failed. There will be times with other tables that data will need to be synchronized where I will not be sure if the data already exists or not. This will also be for third party data warehousing of some information so I'm not sure if all DBA's are all that keen on adding stored procedures as aposed to creating tables separate from their normal data.

|||

OK one PITA thing about this is that I am using ODBC to make the client side SQL queries and the return from the UPDATE is the same when the record does or does not exist. I guess I'll have to either change to a stored procedure or live with errors from attempting an INSERT either before or after the UPDATE. I Think adding a SELECT will do little to enhance performance because it will at a minimum be the same number of statements.

|||

You need to perform the transactions under serializable isolation level for concurrent calls to work. Otherwise none of the methods suggested in the other posts will work. See the thread below for more details on how to implement it using locking hints:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384227&SiteID=1

Best practice for writing own system procedures

Hello,

I'm searching for a best practice or other documentation for writing my own 'system procedure'.
I want to write procs which I can call in the context of every database without using a database name analogous to sp_who for example.
I read about the 'Resource Database'. All system procedures are stored in that readonly database and appear logically in the sys schema of every database.
But I couldn't find documentation about writing my own 'system procedure'.
I discover so far that procs with prefix sp_ stored in the master database do what I want. But is that the only way or is there a better way to do it?
In other threads I read that it is recommended not to use sp_ as prefix for procedures

Wolfgang?

It's not recommended to use the sp_ prefix because there is a slight performance hit if you use it for user stored procedures in a database other than master -- this is because SQL Server will look in master for the stored procedure, if it sees the sp_ prefix. However, if you're creating "system" stored procedures that should be callable from all databases, and which are created in master, then the sp_ prefix might make sense...

There are really no best practices I know of, that apply only to stored procedures in master. They follow the same basic rules as any other stored procedure. Note that you can't create objects in (or even access) the resource database -- it is hidden so that only the query engine can access it.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||Just in addition to Adam, the performance hit will be caused from the Cache Miss that is produced if the procedures takes the sp_ prefix, a schema lock on the procedure and a recompilation of the procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your answers.
As only procs with prefix 'sp_' stored in master are callable from all other databases I assume that that is the right way to write my own "system" procedures.
But I'm not very happy with that approach. I don't like to store ny own procs and tables in the master database because master is a central database for the whole server.

Wolfgang|||

You would have to define "system stored procedure" first. Very particularly, a system stored procedure has a prefix of sp_, is written by Microsoft, shipped with the product, and has special rules for name resolution.

I believe you are asking about "administrative stored procedures", these are procedures that you write that perform actions you need against one or more databases within a SQL Server. For these, I use an administrative database on the instance. I usually call it admin. In that database, I put all of my administrative procs along with any supporting tables, views, functions, etc. Any procedure can be called from any database, you just have to fully qualify the procedure.

|||OK, then talk about "administrative stored procedures".
Unfortunatly your approach doesn't fit my requirements. When I execute in a database kunktest1 'admin.dbo.kunkproc' I'm in the context of database admin and not in the kontext of database kunktest1. But I need to be in the kontext of the database from where I call the "administrative stored procedure" to select the objects of that database for example.

Wolfgang|||Normally you will be able to access the tables in the other databases using the three part names of the objects, like DatabaseName.OwnerOrSchema.Objectname. If this is not feasible for you and you really need the context of the database I would suggest generating a procedure in each database customized for each database.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

Best Practice for Domain Account for SQL Services?

Hello,

I've done some searching, but have found no definite answer yet. Our SQL 2005 servers are members of Active Directory Services. We want to run SQL services using an ADS account.

I see 7 SQL services in the SQL Server Configuration Manager: Integration Services, FullText Search, SQL Server, Analysis Services, Reporting Services, Browser, and Agent.

Question: Is it a bad move to run them all using the same domain account? I mean, wouldn't this give, say the Browser service, more privileges than it needs by allowing its account access to the same resources as, for example, the Agent service? What I'm concerned about is a vulnerability in one service compromising another service.

I would like to be able to use one domain account for all 7 services on two SQL servers, but I have a feeling this is a poor choice.

What is the best method for running SQL services using a domain account?

Thanks,

-Tony

Perhaps these articles will help you in your decison process:

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557

Often, SQL Agent needs a higher level of access to network resources than the SQL Server Service. It is a good practice to provide the minimum level of access to local and network resources as is required for the task. It would not be unusual to have multiple domain accounts for the various SQL Services. In fact, for those services that require network resources, some folks will have separate domain accounts for each server. I have seen every SQL Agent service running under a unique account -the theory is if one server is compomised, unique accounts protect the remaining servers from compromise.

It is a balance between amount of security desired vs. amount of effort required to maintain that security. (As always...)

Beginner question Searching table

I have a stored procedure that takes 5 parameters. Based on what parameters are passed i want to return a result set to the calling code. Not all of the parameters are required so there will be some combinations on the entered parameters.

Do i need to dynamic build the query with the IF statements to check if the input parameter is NULL or not ?

Thanks

You can use the isnull function like so:

-- Using the northwind database in sql server 2k

declare @.categoryname varchar(100)
declare @.categoryID varchar(100)

--set @.categoryname = 'Produce'
set @.CategoryID = 4

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

|||For additional reading on this subject, check this article for a full description of was to do this: http://www.sommarskog.se/dyn-search.html|||

Hi,

Instead of running the below one

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

Running this one may be faster

select * from categories
where (@.categoryname is null or categoryname = @.categoryname)
and (@.categoryID is null or categoryID = @.categoryID)

Since during the execution plan creation the engine may decide a better way since it knows that "@.categoryname is null" is true for every record. But a similar approach is also valid for the first one too. Actually this is related with how wise the sql server engine is.

Eralper

http://www.kodyaz.com

|||

Thanks all for helping. It works great now and it runs fast for me. Thanks so much for you help.

Guess i need to start reading up on my T SQL.

|||

The query optimizer doesn't quite optimize the conditions using the multiple variables like you described. The problem is that the value of only @.categoryname or @.categoryid will be fixed at compile time depending on the first execution and the plan primed into the cache will not be that efficient for the other invocations. Erland's article that Louis posted goes through lot of techniques and their various pros/cons. The recompilation whitepaper below is also a good read on how plan caching works:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx