Protean

Remember when you use a Stored Procedure as the OLE DB Source that you need to turn off the ‘x many rows affected’ statement at the end.

Do this by including in the CREATE PROCEDURE script the statement:

SET NOCOUNT ON

Also, ensure that you make the full call to the procedure:

EXEC spStoredProcedure @myString = ?, @myInteger = ?

This makes parameter mapping much easier, however, don’t expect to be able to preview the data… this only seems to work at run-time.

We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.

Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.

The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.

There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…

(more…)

Ever had a multitude of SQL Agent jobs with dependencies on one another in order for your nightly batch to succeed? Ever had a situation where you forgot to set the steps correctly within the job resulting in the batch failing or not even running at all?

Me too… here’s what I did in order to protect myself from human error.

(more…)