SSIS Package Scheduled under a SQL Job

Question

SSIS Package Scheduled under a SQL Job

Viewing 6 posts - 16 through 20 (of 20 total)

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

ladykythe2003 Newbie Points: 7 More actions · Answer 1

Hi, I'm having the same problem with my SSIS package. When I execute it inside BIDS it works perfectly. I deployed it in the SQL server and execute it, it was successful. So I created a job to execute it, it gives me an error and the worst description of error there is "running package failed". How I am supposed to know at what specific part of the package the error occured? I tested it again and found out that the error was in the connection manager of the db because I'm using SQL server account and it can't retain the password.

Can somebody help me. Please!

bob.aroo-683345 SSC-Addicted Points: 433 More actions · Answer 2

I have also started to experience this problem. I have a number of packages that read from an excel file into a SQL staging table. For about six months, they took three or four seconds to run and were always successful (as long as nobody had the excel file open).

Three weeks ago they all started taking ninety two or ninety three seconds to run. I had not modified or redeployed any packages around the time the problem occured or installed any service packs. The packages still complete successfully.

I have copied the excel file to the SQL Server to rule out network issues and there is no difference.

The package still only takes three or four seconds to run on my (BIDS) development server.

I call the packages using a stored procedure and when I run this in a query window I see the following (note the time difference between started and progress lines):

Microsoft (R) SQL Server Execute Package Utility

Version 9.00.3042.00 for 32-bit

NULL

Started: 14:11:32

Progress: 2009-02-02 14:13:03.31

Source: Import Sales

Validating: 0% complete

etc., etc.

If anyone can help with this, it would be much appreciated.

Note: I do not use SQL agent. The sp that runs the package is always called manually.

Amit Sunkuru Mr or Mrs. 500 Points: 527 More actions · Answer 3

Hi,

I am not sure whether this would work but try the below on the Machine from which your run SSIS:

1. In Internet Explorer, go to Tools --> Options --> Advanced

2. Uncheck the tickbox "Check for publisher's certificate revocation"

3. Apply the settings

Now try executing your package.

- Amit

sqldba-983425 Newbie Points: 7 More actions · Answer 4

Amit,

Did you ever get a rsponse regarding this question?

I am noticing the same thing. I created a job that executes a package ( it does not seem to matter how the package is stored). The first step in the package writes out a audit record to a table with the current date/time.

I've noticed between 30 to 45 seconds difference between the time the job starts and my audit record.

Any ideas???

Kim

Amit Sunkuru Mr or Mrs. 500 Points: 527 More actions · Answer 5

Hi Kim,

Please check my previous post in the same chain before your post.

Hope that helps.

-Amit

b0b555 Mr or Mrs. 500 Points: 591 More actions · Answer 6

Amit's solution works a treat.

For more info on what causes the problem and why the fix works you could start by taking a look at:

http://blogs.msdn.com/michen/archive/2007/07/29/network-causing-ssis-package-startup-delays.aspx

I changed this option on the service accounts that were running the SQL service and the SQL Agent service.