Writing cursor Query messages to a text file in SSIS

Question

Writing cursor Query messages to a text file in SSIS

j.portolese

SSC Enthusiast

Points: 164
More actions
January 4, 2012 at 3:59 pm

#265896

Hello all,
I have a sql task that runs a cursor as part of my ssis package. I originally wrote the cursor to run manually and spit out some output messages as it moved through the records. I then put the cursor step in a sql task and no longer have access to the messages (useful for troubleshooting). Is it possible to use a ssis object (script task?) to write the messages created as a result of the cursor being run to a text file?
Thanks in advance,
JP

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Have you considered writing those messages to a table and then bringing that information into the package if necessary at a later point?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

j.portolese SSC Enthusiast Points: 164 More actions · Answer 2

Jason,

Thanks for the reply. Haven't done much like that but it certainly sounds like it would work. Most of the messages are print statements such as "record number xxxxx has been inserted" or "Can not insert record number yyyyy". Not sure how I would get print statements into a table being as unformatted as they are.

Any suggestion?

Thanks

Jamie

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Change them from print statements to insert statements. You apparently have the prints built into the logic, so replace appropriately with insert statements. Create an runlog table to suit the needs and then just use insert statements.

But I do have to ask, why is this cursor based and not set-based?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

j.portolese SSC Enthusiast Points: 164 More actions · Answer 4

Thanks I'll look into the insert statement idea. Sounds like it would work great.

Cursor because I'm processing records in a change table (insert, updates, and deletes) might not be the most effective use of SQL but it works. The cursor brings in each change record and processes it depending on it's values.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

I see. Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events