none
how to run data flow tasks parallely & mprove performance.

    Question

  • Hi,

    My package contains 8 data flow tasks which performs lookup and load data in to 8 tables.i have connected 8 data flow tasks from execute sql task.When i run the package some of the data flow tasks are failed.I have disabled some of them and run again it ran successful.Then i tried connecting one data flow task to another data flow task this also ran successful.My question is how to improve performance and why when all the data flow tasks are connected from execute sql task some of the data flow tasks are failed.If anybody has encountered such situation please help me .

    Thanks in advance

    Saturday, June 29, 2013 8:38 PM

All replies

  • Could you post the error that you got when they all run parallel?

    That will help to identify the problem, but here is some general info:
    SSIS is an in-memory ETL engine. So it needs (a lot of) memory otherwise it will slow down or fail. You should try to reduce the need of memory by optimizing your data flows:
    http://sqlcat.com/sqlcat/b/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx
    http://www.mattmasson.com/2012/02/resources-for-ssis-performance-best-practices/


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Saturday, June 29, 2013 9:23 PM
    Moderator
  • Hi ,

    I got the below error

    ADO_SRC [1941]] Error: System.Data.Odbc.OdbcException: ERROR [IM005] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
       at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
       at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.Odbc.OdbcConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
       at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

    Sunday, June 30, 2013 12:22 AM
  • Hi ,

     I am using Ado.net  as source and using odbc driver to connect to source database and using my credentials to connect to source.The issue is one data flow tasks runs successfully and the second data flow task fails which is also using same ADO.Net source.I got the below error.

    How to resolve the issue.

    [SSIS.Pipeline] Error: "component "ADO_SRC" (1941)" failed validation and returned validation status "VS_ISBROKEN".

    Thanks in advance

    Sunday, June 30, 2013 12:58 AM
  • Your ODBC driver fails. There might be a database resource governor or some other kind of limit you are hitting. Try setting RetainSameConnection=TRUE parameter on the connection manager.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Sunday, June 30, 2013 2:59 PM
  • Thanks for the reply.I tried setting the property to true but i got other errors.

    Error 1:

    [ADO_SRC [1941]] Error: System.Data.Odbc.OdbcException: ERROR [IM005] [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
       at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)
       at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.Odbc.OdbcConnection.Open()
       at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
       at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
       at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

    Error 2:

    [ADO_SRC [1941]] Error: System.InvalidOperationException: NO_DATA - unable to allocate an environment handle.
       at System.Data.Odbc.OdbcHandle..ctor(SQL_HANDLE handleType, OdbcHandle parentHandle)
       at System.Data.Odbc.OdbcConnection.CreateStatementHandle()
       at System.Data.Odbc.CMDWrapper.CreateStatementHandle()
       at System.Data.Odbc.OdbcCommand.GetStatementHandle()
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
       at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper

    I don't understand what the issue is.Please let me know how can it be resolved.

    Thanks in advance

    Sunday, June 30, 2013 8:20 PM
  • THe issue is your database or driver.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Sunday, June 30, 2013 8:28 PM
  • What is the source? Perhaps there are alternatives for you ODBC driver.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Monday, July 01, 2013 6:03 AM
    Moderator
  • Thanks for the reply.My source is KB_SQL.

    Monday, July 01, 2013 5:59 PM
  • Thanks for the reply.My source is KB_SQL.

    What is KB_SQL ? Please provide more details who is the vendor, version, etc ..

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, July 01, 2013 6:09 PM
  • KB is Knowledge Base Systems and we use razorSQL as SQL query tool and version :RazorSQL v.6.0.5
    Monday, July 01, 2013 8:13 PM
  • KB is Knowledge Base Systems and we use razorSQL as SQL query tool and version :RazorSQL v.6.0.5
    This is not the database. This is a tool for querying data. What is the database and version you are connecting to?

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Monday, July 01, 2013 8:16 PM
  • This is a problem with the KBS ODBC driver.  You should contact them to determine the cause of the issue.

    Monday, July 01, 2013 8:29 PM