Package hanging on OLE DB Command with large result sets

I have an SSIS package (SQL 2005 SP2 and Visual Studio SP1) that does the following:

OLE DB Source --> Conditional Split --> OLE DB Command #1 --> OLE DB Command #2

The source reads from database A. Each row is variable-width and up to several KB wide, including two ntext columns.

Command #1 executes a stored proc in db A, using a bunch of inputs and two output parameters.

Cmd #2 executes an update in db B, using the two output params from cmd #1 as inputs.

When the rowset size is small, around 500, everything works fine.

However, when the rowset size is larger, around 5000, SSIS hangs when trying to execute cmd #2. The profiler shows that none of the cmd #2 updates are ever executed. No error messages are produced, and the connection never times out -- it just hangs forever.

If I replace the cmd #2 updates with a simple select, everything works fine. If I replace it with a stored proc that does an update, it hangs.

The work-around I came up with was to create a new table in db B, and do inserts into the table, but unless I'm missing something, this still seems like a bug...

Do you have the RetainSameConnection property set to true on the OLE DB connection manager you are using? Try that if not.

Package doesn't run anymore on SP2

Hi all,

I've installed SP2 on my server. If I run the package on the server, the package worked there before the installation of SP2. Now with SP2 it doesn't work anymore. In VS2005 on my computer the package works before and after the installation of SP2.

He gives an error with the execution of a sql-task on an oracle server:

Error :Executing the query "insert into cube_content values (trim(?), trim(?), trim(?), trim(?), trim(?), sysdate) failed with the following error: "ORA-01401: inserted value too large for column". Possible failure reasons: Problems with the query, "ResultSet property not set correctly, parameters not set correctly, or connection not established correctly.

Anybody an idea?

Thanks,

Dennis

That error appears to be from the Oracle server, not SSIS related. It looks like you are inserting a value too big for the field.

Look at the table definition and the data you are trying to insert.

|||

It seems as though the interface to Oracle has changed. Do you use the Microsoft OLE DB Provider for Oracle? My best guess is that something in SP2 has changed the provider or something. That's my best guess.

Build a new package using the same SQL statement against Oracle and see if it sets it up with different metadata to what is in your existing package.

-Jamie