Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

Making the case for expressions instead of parameterised SQL (and vice versa) : SSIS Nugget

[Although I colloquially refer to myself on this blog as “SSIS Junkie” it won’t have escaped the notice of regular readers that I haven’t been talking about SSIS much lately, in fact nothing of real substance in regard to SSIS has appeared here since Nesting variables to calculate values on 24th October. The reason for that is that I simply haven’t been using SSIS in my day job so I had nothing to talk about but I recently joined a new project where I am back in my SSIS comfort zone so you may expect a bit more SSIS blogging in the coming weeks/months.]

I’m on record as saying that I love the power that expressions give us in SSIS and I often advocate using them to construct dynamic SQL in an Execute SQL Task by putting an expression on the SqlStatementSource property like so:

SqlStatementSource property

because doing that insulates you from the nuances of using OLE DB parameters. The argument against using expressions in favour of parameterised SQL is that the result of an expression is limited to 4000 characters which can, in some scenarios, be very limiting and may also cause seemingly arbitrary errors that are darned difficult to track down. Furthermore, parameterised SQL increases the chance that the query optimiser can reuse an execution plan thus performance can be improved.

[N.B. Using parameters eliminates the risk of SQL Injection as well although whether that is a risk or not in a SSIS package is not going to be debated here.]

There is actually another reason why one might wish to use an expression rather than parameterised SQL. I had an Execute SQL Task that used parameterised SQL in one of my eventhandlers that was failing and when I looked in the output window I saw the following error message:

Error: 0xC002F210 at SQL Log Event OnPreExecute, Execute SQL Task: Executing the query "EXEC csp_LogEvent ? --LoadHistoryID
, 'PreExecute' --EventType
, ? --SourceName
, 'Starting' --EventDescription
, 0 --EventCode
, ? --UserName
, ? --MachineName
, ? --SourceID
, 0 --ExecutionDurationms" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Illustrated further on this screenshot:

SSIS BIDS output window

As you can see in the screenshot the SQL failed because of a foreign key violation however the value causing the violation was passed in using a parameter (its the LoadHistoryID parameter) and this error doesn’t tell me what that value is so I’m a bit lost when trying to debug it.

Let’s compare that to the error message that I get if I change the Execute SQL Task to use expressions rather than parameterised SQL:

Error: 0xC002F210 at SQL Log Event OnPreExecute, Execute SQL Task: Executing the query "EXEC csp_LogEvent -1 --LoadHistoryID
, 'PreExecute' --EventType
, ‘SQL Begin Load’ --SourceName
, 'Starting' --EventDescription
, 0 --EventCode
, ‘INT\jamie.thomson’ --UserName
, ‘TDD80DEV83’ --MachineName
, ‘{30A418C5-65E0-4AB3-9B67-3B9C07B7479F}’ --SourceID
, 0 --ExecutionDurationms" failed with the following error: "Exception from HRESULT: 0x80040E14". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

SSIS BIDS output window

Its the same error, but this time I can see all of those parameter values (which I’ve highlighted) and in this case I can see that LoadHistoryID=-1 which I happen to know will cause an error. Problem solved!

So all looks good, you should use expressions all the time right? Not so fast! Before you rush off and starting ditching all of your parameterised SQL take a closer look at the two output window screenshots. In the case of using parameterised SQL then we get a decent error message “The INSERT statement conflicted with the FOREIGN KEY constraint FK_Event_LoadHistory” but when we use expressions to build our dynamic SQL that error message doesn’t appear which means it won’t appear in our log file either!

Why is that? Well, our trusty friend SQL Server Profiler has some clues. When executing using dynamic SQL we see the following in Profiler:

EXEC csp_LogEvent 12 --LoadHistoryID
, 'PreExecute' --EventType
, 'Master' --SourceName
, 'Starting' --EventDescription
, 0 --EventCode
, 'INT\jamie.thomson' --UserName
, 'TDD80DEV83' --MachineName
, '{057DAA45-3851-4E2F-A9CF-EA90E3FFE6E0}' --SourceID
, 0 –ExecutionDurationms

When using parameterised SQL we see the following:

exec sp_executesql N'EXEC csp_LogEvent @P1 --LoadHistoryID
, ''PreExecute'' --EventType
, @P2 --SourceName
, ''Starting'' --EventDescription
, 0 --EventCode
, @P3 --UserName
, @P4 --MachineName
, @P5 --SourceID
, 0 --ExecutionDurationms',N'@P1 int,@P2 nvarchar(6),@P3 nvarchar(17),@P4 nvarchar(10),@P5 uniqueidentifier',12,N'Master',N'INT\jamie.thomson',N'TDD80DEV83','3735307B-4144-3441-352D-333835312D34'

The parameterised SQL results in a call to sp_executesql of course which gives us back an error message whereas the straight SQL does not. I’m not going to pretend that I know why that is because if I’m honest that’s outside the bounds of my knowledge – feel free to add a comment if you want to elaborate on this.

The obvious next step is to get the best of both worlds by dynamically generating a call to sp_executesql like so:

ssis dynamic sql execute sql task

which, fantastically enough, results in the following output when we execute:

Error: 0x0 at SQL Log Event OnPreExecute: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Event_LoadHistory". The conflict occurred in database "DCP_ETL_Repository", table "dbo.LoadHistory", column 'LoadHistoryID'.
Error: 0xC002F210 at SQL Log Event OnPreExecute, Execute SQL Task: Executing the query "exec sp_executesql N'EXEC csp_LogEvent @P1 --LoadHistoryID
, ''PreExecute'' --EventType
, @P2 --SourceName
, ''Starting'' --EventDescription
, 0 --EventCode
, @P3 --UserName
, @P4 --MachineName
, @P5 --SourceID
, 0 --ExecutionDurationms',
N'@P1 int,@P2 nvarchar(6),@P3 nvarchar(17),@P4 nvarchar(10),@P5 uniqueidentifier',
-1,
N'Master',
N'INT\jamie.thomson',
N'TDD80DEV83',
'{057DAA45-3851-4E2F-A9CF-EA90E3FFE6E0}'"

ssis output window error message dynamic sql

Cool! We get an error message AND we get the parameter values - exactly what we want. The only thing I’d say is that dynamically generating a call to sp_executesql is a decidedly difficult thing to do (think: apostrophes, parameter length guesswork) and therefore incredibly error prone so think twice before going down this route; and don’t forget my point about the result of an expression having a maximum length of 4000 – that’s tripped up many a good SSIS developer

Hope this helps! Comments are welcome!

-Jamie

UPDATE 14th April 2009: You would do well to also take a read of my later blog entry ExecuteSQLExecutingQuery which provides another very useful method for logging dynamic SQL.

Published Monday, December 08, 2008 1:35 PM by jamie.thomson
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems