Post reply

SSIS Package Scheduled under a SQL Job

  • April 28, 2008 at 11:48 pm

    #808121

    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!

  • February 2, 2009 at 7:30 am

    #936418

    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

    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

    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.

  • February 2, 2009 at 9:41 am

    #936523

    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

  • April 30, 2009 at 12:30 pm

    #986847

    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

  • May 1, 2009 at 12:22 am

    #987162

    Hi Kim,

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

    Hope that helps.

    -Amit

  • October 13, 2009 at 1:24 am

    #1065056

    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.

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

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