Post reply

SSIS very slowly

  • April 8, 2009 at 7:02 am

    #129787

    Hello,

    I have a SQL2000 environment with about 80 DTS packages to extract data from Oracle, I need to migrate this environment 2005 x64, so I'm migrating the DTS packages to SSIS, the problem is the time of execution of SSIS packages that take up to 2 hours while DTS in took about 10 minutes. Other packages that take less than a minute now in SSIS that takes over 30 minutes. Who knows why this happens, or is a problem in my environment. If this is a reality is totally unfeasible migrate my environment for 2005. I use OLE DB and OLE DB SOURCE DESTINATION to extract the data.

    Sorry my bad English ...

  • April 9, 2009 at 7:00 am

    #974999

    could anybody help me please

  • April 9, 2009 at 7:10 am

    #975007

    The only hypothetical reasons that i can think of from the desc of your problem are

    1. network throughput: maybe u r maxing out on this or not using it right.

    2. Query Optimizations (source extract optimizations): Optimize your sources\extracts. Minimize locking and blocking from your source extracts by using NOLOCKS etc

    3. use of parallelism in data flows: (if u have enough memory).

    Also, is your SSIS server and SQL Server on the same machine, if so, in the event of a contention for resources, SQL Server will take precedence. Watch out for SSIS transformations leaking to disk (make maximum usage of memory).

    these are guidelines to tune your packages. u will find more out there.

  • April 9, 2009 at 7:31 am

    #975022

    thanks for your answer,

    but I can't use nolock because I extract data from Oracle and I extract all rows of tables.

    My SSIS Server and SQL Server are in the same machine but don't see contention of resource.

    Are There any documentation about this? SSIS more slow that DTS?

    I Always use DTS to extract this informations and never I had problem of performance

    Thanks

  • April 9, 2009 at 7:36 am

    #975030

    Have u used options like Fast load in the data flow task, optimizing the oledb source adapters to allow more network packet size (to 32K) instead of the default 4K size. optimize your commit sizes suitable to your environment (dont leave it to 0)

  • April 10, 2009 at 3:02 am

    #975614

    Eudes

    I also had a number of DTS packages importing flat textfiles developed in SQL Server 2000.

    Instead of SSIS I decided to use the option in SQL Server 2005 to run DTS.

    Search for SQLServer2005_DTS.msi on MS site. This works as a dream on a 64 bit machine.

    /Gosta

  • April 10, 2009 at 7:02 am

    #975724

    The problem is simply migrating the DTS packages to SSIS. I had same problem about 3 years ago and even worse. For long term, you have to modify each of your 80 packages, assuming each one has its own problem.

    The best thing you can do now is to check ODBC drives in your packages, and they are very likely using Microsoft ODBC drive to Oracle. Instead of using this drive, simply choose Oracle drive to Microsoft if it’s available.

  • April 13, 2009 at 6:07 am

    #976484

    Gosta Munktell

    I know the SQLServer2005_DTS.msi, but to use this solution I need one server to sql2000 to run my packages DTS, and another server to my solution with sql2005. right?

    Thanks

  • April 13, 2009 at 6:28 am

    #976500

    Chris Yannix

    I always used OLEDB Provider "Microsoft for oracle DB" with this package my process took 40 minutes to execute, now I changed my packages to use "Oracle provider for oleDB" and took 35 minutes, but my packages in DTS took 7 or 8 minutes ... the difference is very big between DTS and SSIS ...

    Thanks by answer

  • April 13, 2009 at 6:43 am

    #976518

    Hi eudes - there are a number of 'issues' with SSIS performance, but the commonest one I've come across which may be an issue with your migration from DTS to SSIS is where you have a datasource defined directly on a view or table. This for all but the most trivial result sets is a screaming nightmare.

    Contra intuitive as it may be, if, for example you have MyTable as a source - changing to to a SQL Command with SELECT col1, .... coln FROM MyTable can provide serious performance gains. As understand it, the issue is the way the former is implemented under the hood (a bug, in other words. Or 'by design' in Microsoftspeak).

    hth

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • April 13, 2009 at 7:14 am

    #976555

    Hi Andrew

    I have used "select col1, col2 from my table" this improved my performance a little, but yet is very bad ...

    Thanks ...

  • April 13, 2009 at 7:42 am

    #976603

    I know the SQLServer2005_DTS.msi, but to use this solution I need one server to sql2000 to run my packages DTS, and another server to my solution with sql2005. right?

    You just need the SQL Server 2005. You can skip 2000.

    //Gosta

  • April 14, 2009 at 6:10 am

    #977149

    Gosta,

    but, In this case my package will be recorded in my MSDB database?

    Can I work with several packages as in SQL 2000 and I will not need of my SQL 2000?

    I thought I knew this SQLServer2005_DTS.msi

    Thanks so much

  • April 14, 2009 at 7:12 am

    #977189

    "but, In this case my package will be recorded in my MSDB database?"

    What du you mean?

    In may case the DTS packages where designed in server 2000 and saved as

    etc.dts files.

    These files can then be opened in SQL server 2005 (the SQLServer2005_DTS.msi

    is installed) . You can modify the content of each DTS packagage and save it.

    I dont have SQL-server 2000 installed on the machine runing these DTS packages.

    /Gosta

  • April 14, 2009 at 7:25 am

    #977194

    OK,

    I asked if the packages are saved in the msdb database.

    but I must save as dts file.

    how can I schedule these packages?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply