Data Flow Task Stuck

SQL Server Integration Services

Corey M.


I have a simple data flow task setup...
2 ADO.NET connection managers, each referencing a DSN pointed to a Unidata database.
2 DataReader sources, each using a single ADO.NET connection managers, running a simple SELECT statement from a table.
I have a Union All transform setup to merge the data and write to a OLE DB Destination (SQL05 database)

When I run the package, each source will validate, but only one will execute. The other source will do nothing. The data source will be colored yellow, and will just sit there. The package will just sit, almost like it is waiting for input.

This behavior is not consistent, however. It varies which data source will hang, pretty much 50-50. About 25% of the time, both sources will execute, and all rows will be written to the destination.

Any help is appreciated.

thanks

Top

Re: Data Flow Task Stuck

jwelch


Are the source connections pointing to the same database I'm not familiar with Unidata, but it sounds like there might be some type of blocking issue, either in the database or in the ODBC driver. If you use two separate data flows, one per DataReader Source, does it consistently run successfully




Top

Re: Data Flow Task Stuck

Corey M.

No, they are not pointing to the same database, and I have tried independent data flows with the same results.






Top

Re: Data Flow Task Stuck

jwelch

When you tried independent data flows, did you add a precedence constraint so that they were executing sequentially rather than in parallel

If the problem shows up when you are only running a single DataReader Source, I'd see if there are any known issues with the ODBC driver.





Top

Re: Data Flow Task Stuck

Corey M.

When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio


Top

Re: Data Flow Task Stuck

Phil Brammer

Corey M. wrote:
When I try them sequentially, it works correctly every time.

This is an acceptable workaround, but I am still curious to determine the reason for this strange behavio

My guess is that it's an ODBC driver issue. Perhaps table locking or something like that. Who knows. You'll have to check for any known issues with the ODBC driver provider.





Top

Re: Data Flow Task Stuck

jaegd

You may wish to try the flowsync transform, placing one FlowSync transform between each source adapter and the union all transform (for a total of two FlowSync transforms).

The FlowSync transform is a speed governor, so a particular source flow doesn't get too far ahead of the other one. For something like union all, one source outpacing another should not be an issue, but you could certainly give it a shot.

To install the transform, you'll need to copy the assembly (FlowSync.dll) into two places and add it to the toolbar.

1. For runtime purposes, copy the FlowSync.dll into the global assembly cache, which is "%windir%\assembly" or typically the directory c:\windows\assembly. For design-time purposes, copy the same FlowSync.dll into the Integration Service PipelineComponents directory, which is typically located at "%ProgramFiles%\Microsoft SQL Server\90\DTS\PipelineComponents", If you're running 64 bit, the design-time installation directory is typically located at "%ProgramFiles(x86)%\Microsoft SQL Server\90\DTS\PipelineComponents".

2. To add flowsync transform to the toolbar, open up BIDS, right click on the toolbox, select "Choose Items...", go to the tab SSIS Data Flow Items, and Select the FlowSync transform, which will then show up appear in the toolbox as an available transform.


Top

Re: Data Flow Task Stuck

Jeremy D.

Hi Corey,

Mind if I ask you where you got an ODBC driver to access the Unidata database I'm needing to get into one from SSIS myself, but have been unable to locate a driver.

Thanks for any help you can provide!

Jeremy


Top