Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Integration Services

Works fine inside BI Dev. Studio, but fails when scheduling it in SQL server 2005.

Hi!

We are currently developing a project in SQLIS using the BI dev. studio. The package executes with no problems in the development environment, but when I try to deploy it and execute it using the SQL Server Agent, the package fails.
"Work flow"
--

01. Build a package deployment utility (Rebuild all).
02. Runs the deplotment utility.
03. Checks that the latest package has been installed.

04. Creates a new job in SQL Server, using the SQL Server Agent.
05. Adds a new step to the job.
06. Sets that it is a DTS package that should be run ("file-based")
07. Sets the location of the package.
08. Saves the job.

09. Starts the job manually

After step 9, the job runs for a while until it stops...

I've tried several different setups, disabling parts of the package, and after I've disabled some part of the package it run. But the next time I start it fails again...

All software is run on the same server. I'm using the "Base installation" of SQL Server 2005.

Hope someone can point me out in some direction...

Best regards,

Mattias

[1098 byte] By [mattis] at [2007-12-16]
«« its no longer possible to use a 'different' build configuration
»» Establishing a TCP connection in the emulated environment
# 1
More information here would be helpful. What error message do you get?

[To get an error message enable logging on your package]

-Jamie

JamieThomson at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 2

Thanks, now I have enabled logging on the package in BI Stutio. How do I set the configuration string for the "DTS Log Provider for Text Files"?

This is the error message I get in the history:

--
The return value was unknown. The process exit code was -1073741819. The step failed.
--

Current software used:

Microsoft SQL Server Management Studio 9.00.852.00
Microsoft Analysis Services Client Tools 2000.090.0852.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.40607.42
Operating System 5.2.3790

/Mattias

mattis at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 3

Having similar issue. The job step running an ssis pacakge fails with a -1073741819. But package runs fine from BIDS. I have logging enabled and see no messages pertaining to the ssis package. It's like it never makes it inside the package.

I'm running sql server 2005 for ia64 (64 bit Itanium). This was running fine then we scrubbed the server and reinstalled everything and added sp1. Now three out of the nine jobs I was running get the above error.

The failing jobs run ssis packages that pull data from our db2 mainframe. These particular packages all have where clauses in the select statement that pull the data. All the other jobs executing successfully are running packages that are doing a full dump with no where clauses in the select.

Marjorie at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 4
Is your package file based? If so, does the SQL Server Agent have the right permissions to access the files in the folder in which they reside? It seems that if something runs okay in the BIDS and then doesn't when your try to run it as a scheduled job, it is usually permissions that are the problem.
swells at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 5

If you are not getting anything in the SSIS log, then it must be prior to that, and file permissions are a good one. Use the SQL Agent step log, and a CmdExec step. More on the why here -

Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

DarrenSQLIS at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 6
No, not file based. The permissions are no different from the other jobs that are running successfully.
Marjorie at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 7

Have you tried setting the ProtectionLevel to DontSaveSensitive?

I ran into issues running SSIS packages with SQLAgent until I did this.

Thanks,

Tristan

tristanb at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 8
I use the ServerStorage default. Which has never given me a problem before and is being used in six other jobs that are running successfully on this server.
Marjorie at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 9
So what errors are you getting? The job step log will give something if using a CmdExec step.
DarrenSQLIS at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 10
I'm having a similar error, but the BIZARRE thing is that my job completes all the actions, then fails reporting 'Process Exit Code -1073741819' problem.
I have a package (Orchestrator) that calls 3 seperate 'children' packages. The 'children' packages each have to:
1. Check for existance of a file (.Net script component, pre-compiled = true).
2. If exists, then purge staging tables (truncate)
3. If purge succeeds then is data flow task that does the following:
3.1 Open excel source
3.2 use conditional split to route 'bad' data to error table, and 'good' data to data conversion task
3.3 convert data into correct data type for target staging table. Any failures here are redirected to the same error table as 3.2
3.4 insert (fast load) into target staging table
4. use file system command tomove excel files to archive location.
I am executing the package in a step, using CmdExec, with a DTEXEC commandline. The packages do logging to SQL table.
Currently, my package has succeeded, and reported the following message:
--
Executed as user: domain\SQLService. ...06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 18:26:32 Info: 2006-07-28 18:26:49.84 Code: 0x4004300A Source: Import PriceCheck Excel Spreadsheet DTS.Pipeline Description: Validation phase is beginning. End Info Progress: 2006-07-28 18:26:49.84 Source: Import PriceCheck Excel Spreadsheet Validating: 0% complete End Progress Info: 2006-07-28 18:26:49.84 Code: 0x4004300A Source: Import BuyList Excel Spreadsheet DTS.Pipeline Description: Validation phase is beginning. End Info Progress: 2006-07-28 18:26:49.84 Source: Import BuyList Excel Spreadsheet Validating: 0% complete End Progress Progress: 2006-07-28 18:26:49.84 Source: Import BuyList Excel Spreadsheet Validating: 12% complete End Progress Progress: 2006-07-28 18:26:49.84 Source: Import PriceCheck Excel Spreadsheet Validating: 12% complete End Progre... Process Exit Code 0. The step succeeded.
--
and 30 minutes later, it fails with:
--
Message
Executed as user: domain\SQLService. ...99.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 18:35:24 Info: 2006-07-28 18:35:42.37 Code: 0x4004300A Source: Import SalesRank Excel Spreadsheet DTS.Pipeline Description: Validation phase is beginning. End Info Progress: 2006-07-28 18:35:42.37 Source: Import SalesRank Excel Spreadsheet Validating: 0% complete End Progress Progress: 2006-07-28 18:35:42.37 Source: Import SalesRank Excel Spreadsheet Validating: 12% complete End Progress Info: 2006-07-28 18:35:42.39 Code: 0x4004300A Source: Import PriceCheck Excel Spreadsheet DTS.Pipeline Description: Validation phase is beginning. End Info Progress: 2006-07-28 18:35:42.39 Source: Import PriceCheck Excel Spreadsheet Validating: 0% complete End Progress Info: 2006-07-28 18:35:42.39 Code: 0x4004300A Source: Import BuyList Excel Spreadsheet DTS.Pipelin... Process Exit Code -1073741819. The step failed.
--
The bizarre thing is that my last execution COMPLETED ALL ACTIONS REQUIRED - i.e. all 3 children packages started, found their files, truncated their staging tables, populated the tables with the data from the excel spreadsheets, moved the files.... and THEN the job failes, with the 'Process Exit Code' There are no errors in the 436 rows in the [dbo].[sysdtslog90] table. All the rows have succesfully been inserted, there are no rows in the error tables. All the job needed do was report success - and yet I stare at the -1073741819....

GethWho at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
# 11

"... but the BIZARRE thing is that my job completes all the actions, then fails reporting 'Process Exit Code -1073741819' problem."

We're having the same exact issue on multi-CPU servers.

We contacted MSS and were issued with a HotFix (No KB924345), but it doesn't work.
One of the DLLs does not get updated and the issue persists.
Still waiting for Microsoft :-(

There is a simple work-around (if you can live with it):

  1. Set "MaxConcurrentExecutables = 1" in the Package Properties of all SSIS packages that are failing.

This forces execution of the package on only 1 CPU and the problem disappeared for us.

Cheers,
Saul
Saul at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Server Integration Services...
SQL Server
SQL Server Integration Services

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified