Very nice question, thanks.

Congrats Uwe!

Take some well deserved vacation 🙂

edit: didn't see the post of Robert Davis. I hope you still passed though. Fingers crossed!

I did some more testing and you need to use an ADO.NET connection manager.

Then you can use the Execute SQL Task and put the result set in a string variable.

Ah, so there is already an XML generated inside the stored procedure.

Can't you try to execute the sp directly in an OLE DB source?

abhinav_kapoor (4/25/2012)

---

Fetch PK from source. Use PK as we need a distinct value for each row for Lookup in the destination table. In Lookup task, define ur destination and use...

Robin is right, CLR is the way to go.

Example:

Regular Expressions Make Pattern Matching And Data Extraction Easier

abhinav_kapoor (4/25/2012)

---

You can use lookup in dataflow task in SSIS. After lookup, use conditional split for either updation/insertion in destination table. But in order to achieve this, you should have...

And what is the result set of your stored procedure?

What do you mean with "there is no o/p oaram in SP"?

Also, post the error message.

The Execute SQL Task

Yes. The question is: why?

It seems the error message is stored inside the XML file.

Any reason you don't store the results in a full resultset (object variable) instead of XML?

The Execute SQL Task doesn't fail on an Access Denied error? Strange...

What do you mean with "the stored procedure is unavailable"?

You can start a SQL Agent job in SSIS by using the sp_start_job stored procedure in an Execute SQL Task.