locked
opening connection to external pervasive sql database, in SSIS package, taking 20 sec (when executing by dtexec) RRS feed

  • Question

  • I am new to SSIS. I have created 5 SSIS packages to fetch data from an external Pervasive SQL database to the local SQL Server. Executing these packages from BIDS takes around 10 seconds each. But if I execute them using dtexec it is taking around 30 seconds each. On using the /ConsoleLog switch, the log shows the first 20 seconds being used on opening a connection to the PSQL DB. This is the case while executing each package.

    If I click 'Test Connection' in the Connection Manager from BIDS, it still takes 20sec to display success for the first time.

    Part of the log generate with /ConsoleLog showing this part is given below

     

    Log:
         Name: Diagnostic
         Computer: xxx
         Operator: xxx
         Source Name: xxx
         Source GUID: xxx
         Execution GUID: xxx
         Message: ExternalRequest_pre: The object is ready to make the following external request: 
    'IDbConnection.Open(ConnectionType: Pervasive.Data.SqlClient.PsqlConnection, 
    Pervasive.Data.SqlClient, Version=3.2.0.0, Culture=neutral, PublicKeyToken=xxx
    ConnectionString: Database Name=xxx;Host=xxx;Schema Collection Timeout=180;)'.
         Start Time: 2011-12-30 05:38:16
         End Time: 2011-12-30 05:38:16
    End Log
    Log:
         Name: Diagnostic
         Computer: xxx
         Operator: xxx
         Source Name: xxx
         Source GUID: xxx
         Execution GUID: xxx
         Message: ExternalRequest_post: 'IDbConnection.Open succeeded'. The external request has completed.
         Start Time: 2011-12-30 05:38:37
         En

     

     

    I enabled trace on the Connection Manager. The connection part of the log is given below

     

    Assembly Name:         Pervasive.Data.SqlClient, Version=3.2.0.0, Culture=neutral, PublicKeyToken=xxx
    Assembly File Version: 3.2.37.000537
    
    00000001 08:38:47.918 T1 I54267293 Connection::Open ENTER
    
        ConnectionString:  Database Name=xxx;Host=xxx;Enable Trace=1;Trace File=c:\psql-con.log;
        ConnectionTimeout: 15
    
        Error Generated by Pervasive Provider:
        Message   = DTC is not supported
    
    POOL: Found a pooled connection
    
    POOL: Current status: 1 pools, 0 connections
    
    00000001 08:39:09.098 T1 I54267293 Connection::Open EXIT

    No external configuration file is being used.

    The ProtectionLevel property is set as 'EncryptSensitiveWithPassword' and the password is passed to dtexec using the /De switch.

    Is this 20 sec time normal ?

    The packages are executed in a batch file one after the other. Is there some way to cache the connection or carry forward it to the other packages, from the first executed package ?


    Any help will be appreciated.

    Thanks,

    Sree

    Friday, December 30, 2011 1:54 PM

Answers

  • How did you set the conn to Pervasive SQL database?

    AFAIK it does not support OLEDB, hence you must have used the ODBC that is slower, but in general the whole connection procedure depends greatly on the driver and somewhat more on the network. However, based on what you found it is the slow connection handling on the Pervasive SQL database side. We can try to see if its pool is not in warmed up state by doing this:

    1) Create an external connection to Pervasive SQL database from another process, say a another package connecting just before your other will (of course it will take that start up time), then

    2) You start your important package right after the 1st connected. Observe if the start time was fast/good.

    If we find it was fast, communicate to whoever is in charge for Pervasive SQL database to tweak the conn manager.

    Also, if I were you, I would try to see what other drivers are available in order to see if it can connect speedier.


    Arthur My Blog
    • Proposed as answer by Eileen Zhao Tuesday, January 3, 2012 9:19 AM
    • Marked as answer by Eileen Zhao Thursday, January 5, 2012 6:40 AM
    Friday, December 30, 2011 2:53 PM

All replies

  • How did you set the conn to Pervasive SQL database?

    AFAIK it does not support OLEDB, hence you must have used the ODBC that is slower, but in general the whole connection procedure depends greatly on the driver and somewhat more on the network. However, based on what you found it is the slow connection handling on the Pervasive SQL database side. We can try to see if its pool is not in warmed up state by doing this:

    1) Create an external connection to Pervasive SQL database from another process, say a another package connecting just before your other will (of course it will take that start up time), then

    2) You start your important package right after the 1st connected. Observe if the start time was fast/good.

    If we find it was fast, communicate to whoever is in charge for Pervasive SQL database to tweak the conn manager.

    Also, if I were you, I would try to see what other drivers are available in order to see if it can connect speedier.


    Arthur My Blog
    • Proposed as answer by Eileen Zhao Tuesday, January 3, 2012 9:19 AM
    • Marked as answer by Eileen Zhao Thursday, January 5, 2012 6:40 AM
    Friday, December 30, 2011 2:53 PM
  • Hi Arthur,

    Sorry for being late in replying.

    I am using ADO.net connection for connecting to Pervasive SQL DB. There is Pervasive SQL Data Provider among the .net providers. I don't know if it is there by default in SQL Server or it was added on installing the Pervasive SQL client.

    The Pervasive SQL client installed is 32bit while the machine is 64bit. So the ODBC drivers for Pervasive SQL are 32bit and can't be used in the package. Even if I use the ODBC connection and run the package using the 32bit dtexec, the same delay is there for opening the connection.

    I tried running multiple packages one after the other, but it still takes the 20 sec connection time in each package.

    While I earlier thought that it was running faster in BIDS, it is not the case. When I open a package in BIDS, it immediately opens the connection to Pervasive SQL databases freezing the display for 20 sec. I used the monitor utility of Pervasive SQL and found this connection being opened. Afterwards when a package is executed from BIDS it runs fast, which I suspect is due to this opened connection. But for dtexec there is still the 20 sec delay, even when the package is opened in BIDS. I believe this is because it is a different application.

    I think you are right that this might be due to something at the Pervasive SQL side. I will let the person in charge of that fix this.

     

    Thanks a lot for your help.

     

    Regards,

    Sree

     

    Monday, January 2, 2012 12:08 PM