Troubleshooting SQL Server 2005 Integration Services
If you have problems with SQL Server 2005 Integration Services (SSIS), review this troubleshooting
checklist to find potential solutions.
1. Install the latest SQL Server 2005 service pack.
Because many SQL Server 2005 Integration Services bugs were fixed in SQL Server service packs,
you should install the latest SQL Server service pack.
At the time this article was wrote the latest SQL Server 2005 service pack was service pack 2.
Check this page to obtain the latest service pack for SQL Server 2005:
http://support.microsoft.com/kb/913089/
2. Ensure that the Schedule service is started.
Before scheduling SSIS package, the Schedule service must be started. For example, to start
Schedule service on the Windows 2000 choose:
Start -> Settings -> Control panel -> Administration -> Services
(choose Startup "Automatic" type) and Start.
3. Ensure that the Integration Services service is started.
The Integration Services service monitors and manages SSIS packages. By default, this service
is started and its startup type is set to automatic. If you have problems with SSIS packages
scheduling, check that the Integration Services service is started.
4. Consider setting breakpoints in SSIS package to debug its work.
When you developing SSIS package in Business Intelligence Development Studio, you can set
breakpoints at the package level, or at the level of the individual tasks and containers
that the package includes. Using breakpoints can help you to find potential problems with
the packages.
5. Consider monitoring performance of the data flow engine.
SQL Server 2005 Integration Services includes many performance counters for monitoring the
performance of the data flow engine. Use the Performance Microsoft Management Console (MMC)
snap-in to create a log that captures performance counters.
6. Consider logging SSIS package execution.
SQL Server 2005 Integration Services includes a variety of log providers that allow packages
to log information at run time to different types of files. Information can be logged to text
files, SQL Server Profiler, Application log in Windows Event Viewer, XML files, and the
sysdtslog90 table in a SQL Server database.
7. You may receive an access violation error message when you run an SSIS
package that transfer data between two instances of SQL Server 2005 on
the same x64 computer.
This problem occurs when data flow components from multiple SQL Server 2005 Integration Services
Lookup tasks share the same cache area. To work around this problem, you can configure the SSIS
Lookup transformation to use no caching or you can set the MaxConcurrentExecutables property of
the SSIS package to 1 to prevent parallel execution of SSIS tasks.
8. Executing SQL Server 2005 Integration Services packages that are stored
in the SSIS package store may cause memory leak.
This problem occurs in a 64-bit version of SQL Server 2005 only. To work around this problem,
you can store the packages in SQL Server or in the file system instead of the SSIS package
store. To resolve this problem, install the latest SQL Server service pack.
9. Executing SQL Server Integration Services (SSIS) package in SQL Server 2005
may cause an access violation error.
This problem occurs because a heap corruption occurs during the heap space allocation process.
Usually this problem occurs on a multiprocessor computer. To resolve this problem, install the
latest SQL Server service pack.
10. If you schedule some SSIS packages to run as jobs and when the jobs are
running, you log on to the console session from a remote client computer
then some of the jobs may fail when you log off from the console session.
This problem occurs because the dtexec command prompt utility (this utility is used to configure
and execute SSIS packages) is canceled when you log off from the console session. To resolve this
problem, install the latest SQL Server service pack.
11. Adding a configuration to a SSIS .dtsx package may cause the error message:
"An error occurred while a new configuration was being added".
This problem occurs because the SSIS .dtsx package is not associated with a business intelligence
solution. To work around this problem, you should associate the .dtsx package with a business
intelligence solution (you can make it by using the Solution Explorer in the Business Intelligence
Development Studio). To resolve this problem, install the latest SQL Server service pack.
12. Transferring SSIS package may cause memory leak when you use Fuzzy Lookup
and Fuzzy Grouping.
When this problem occurs a long-running Fuzzy Lookup or a long-running Fuzzy Grouping package
may fail. To resolve this problem, install the latest SQL Server service pack.
13. You can encounter that the login account of SQL Server 2005 Integration
Services changes to Network Service after installing the SQL Server 2005
service pack 1.
To work around this problem, you should manually change back the login account of SQL Server 2005
Integration Services to the account that this service used before you installed SQL Server 2005
service pack 1.
14. When you install SQL Server 2005 service pack 1 on a computer that is already
running SSIS service, the SSIS service will not start.
This problem occurs because the computer cannot reach the http://crl.microsoft.com web site.
To work around this problem, you should configure proxy settings to allow SSIS service to access
the internet. To resolve this problem, install the latest SQL Server service pack.
15. When you run a SSIS package that contains a Merge Join transformation, the
package may stop responding.
In this case, the CPU Usage counter for the DTEXEC process may be zero. To work around this
problem, try to avoid using a Merge Join transformation in your SSIS packages. To resolve this
problem, install the latest SQL Server service pack.
16. Executing SSIS package that calls other child packages may cause error message.
In this case, one or more of the child packages fail. This problem occurs because several
requests cannot own a transaction at a time. To work around this problem, you can set the
TransactionOption property to the Supported value (when this problem occurs, this property
is set to the Required value). To resolve this problem, install the latest SQL Server
service pack.
