For the time being, I've resorted to using the file system approach - in my transaction I use an xp_cmdshell with an echo > file.csv command, which stores the information...

Never mind - that solution has the same problem. I can't process the report until I know all the background records are loaded, but I can't know they're loaded unless...

Okay, fair enough, I'll go into a bit more detail.

My .NET app, when the user requests a report for a given day, fires off a stored procedure. The stored procedure...

The reason why it's split like that is because I want to do everything asynchronously. I'm using Service Broker for that purpose - the VB.NET application queues up a process...

Hah - okay, so I finally found a solution, but boy is it ever convoluted.

The solution:

Use msdb.dbo.sp_start_job to start a job, which is set up as a OperatingSystem (CmdExec) type...

Sorry, I should make a few notes:

I know that another solution is to use OpenXML, however, that requires .NET framework 3.X, while the server has access to only 2.X. And,...

You're trying to send out an email with the records in it? Why not just send an email with the records as an attachment? If you can do that, then...

You're not really giving enough of an explanation. What do you mean by you can't see the data? You mean the data has been deleted, or your report is just...

EDIT ***

Sorry, misread your post.

The Merge transform requires both sets of data to have the same columns. So no, you can't do what you're trying to do with the Merge...

One thing to be careful about whenever you're using dynamic SQL in SSIS - if you're ever thinking of putting that data into a destination table or file through a...

Generally if the thing tells you that you have insufficient memory, then it means you have insufficient memory 😛

Did you try loading all the records from each table individually, into...

Are you getting records in the *source*?

Try running your query directly in an Oracle DBMS and see what it returns. If you are not getting rows from the source, your...

I'm sure you already tried this, but just incase you didn't, does setting DelayValidation to true on each step help at all? And maybe ValidateExternalMetadata to false as well?

Probably your best bet is to put a dummy script component in your foreach loop, and then connect that script component to your real component. In the connector linking the...

Just to add to that comment by Phil, sometimes you won't be able to see the real error because the log file in a job execution limits the number of...