It's the little details that get you down. SQL Server Integration Services are all about extracting, transforming and loading data. The big problem with doing that successfully is handling the data types. You see SSIS has a slightly different set of types to what your SQL database has. Translating between them is done automatically and just like a weakly typed language it can get them wrong. However recently I had a different issue which requires relating the whole saga.

Firstly my package hung, sitting on pre-execute. This alone was vexing for there was no obvious reason. So I turned to the old adage "Let the database handle it". I made stored procedures of my selection queries, and temporary tables to draw from.

Alas an SQL task above my data flow gave syntax errors and dutifully I went to debug. I turned off retain same connection and that seemed to do the trick. I was running, or getting closer to it.

Alas behind one error another, parameter not found in mapping. Thus I went to rename the parameters, to make them whole and clean.

I set up my package around this new way, with an Execute SQL Task, followed by the data flow. And now my data flow would retrieve the data from temporary tables that existed only as long as my connection. Of course this meant I had to turn back on retain same connection. SSIS is diligent in cleaning up after itself.

It is a strange thing but in testing I have found, using parameters in SSIS queries incredibly slows it down. I do not know the vagaries of pre-execute, only the lengths that it must go to. Thus the lesson for today is, when going for complex parametric queries, just use stored procs. Slow they may be, but faster than SSIS.

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
BBCode format allowed