• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

SQL Server 2005 job takes forever to process SSIS package!!! WHY?!

I was able to run the SSIS package in Visual Studio in less than 3 seconds successfully, but when I try to automate this process and use SQL Server Agent to run this package, it takes a very long time.

Its been on the first action for 20 minutes already, does anyone know how I can check to see what the hold up is? Or what I can do to fix this? It looks like it hasn't even process the first Control Flow on the SSIS package.

0
TeknikDev
Asked:
TeknikDev
  • 7
  • 5
  • 2
  • +1
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
What kind of tasks are involved? Is it possible you are doing something that the SQL Agent Service account does not have permission to, but your account does when you tested via Visual Studio?
0
 
TeknikDevAuthor Commented:
It's just a batch file that executes. It just looks for the most recent file in the folder and processes the excel file (data source). The account, i assume is admin
0
 
Molly FaganApplications Team SupervisorCommented:
There's a free version of BI xPress (http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/Default.aspx) that would help you track issues.  The free version is limited to one package.  I haven't downloaded and used it myself but saw demonstrations by the creators (Brian Knight--author of several SQL Server books that I have in my collection--including the one on SSIS).
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Kevin CrossChief Technology OfficerCommented:
Just to be safe, check that the NTFS permissions on the batch file and directory it is checking is accessible by the account under which SQL Server Agent is logging in with. Go to Start > Run > Services.msc and scroll to find the SQL Server Agent service and check logon credentials.

I have not used BI xPress either, but would trust any such tool from SQL Server MVP Brian Knight and PragmaticWorks.
0
 
8080_DiverCommented:
Its been on the first action for 20 minutes already, does anyone know how I can check to see what the hold up is? Or what I can do to fix this? It looks like it hasn't even process the first Control Flow on the SSIS package.

So, how do you know that is has been on the "first action" for 20 minutes?

What is the "first action"?

It's just a batch file that executes.
What do you mean by that?  Is the SSIS package just executing a Batch job?

0
 
TeknikDevAuthor Commented:
yeah sqlagent usa is the account logged in. I just unchecked the directory so its not read only anymore. But its still not moving along. anymore ideas?
0
 
TeknikDevAuthor Commented:
problem is i dont own the box - just have the admin role. So i cant install anything without approval from the higher ups.
0
 
Kevin CrossChief Technology OfficerCommented:
If you just changed permissions, you may have to restart the SQL Server Agent service (i.e., simulate a logout and login of the service account) to pick up the permission change. Regarding the restriction, that is unfortunate as I suspect BI xPress would be useful as I have only seen good things from that group in my SQL community travels.

Anyway, I suspect this is permissions related, but I would confirm things for 8080_Diver http:#37032390 in case there is another obvious cause. If this were T-SQL, though, I would say xp_cmdshell needs to be enabled. I do not believe the same is true for SSIS Execute Process Tasks, but recall service account rights to be the driving factor to it working.
0
 
Molly FaganApplications Team SupervisorCommented:
You can install BI xPress on your box--it's meant to be installed on the developer (or DBA) box versus the production server.
0
 
TeknikDevAuthor Commented:
blah - just restarted with the folders set to read/write instead of read only and still takes a long time....i wonder what it is?
0
 
TeknikDevAuthor Commented:
could it be the protection level settings on the SSIS package?
0
 
Kevin CrossChief Technology OfficerCommented:
0
 
8080_DiverCommented:
@mwvisa1,

From a quick read of the information at that link, it seems to be indicating that the package doesn't run at all rather than that it runs very slowly.

@TeknikDev,
Does the package ever actually complete or are you killing it after an extremely long time?

Also, what else is going on on the server where you have scheduled the job?
0
 
TeknikDevAuthor Commented:
Ok everyone thanks so much for all your help! You have been a great support in this painstaking process.

I was able to resolve the issue on my own and the issue lies in the SSIS package property. I had defined in the "MISC > Work Directory" property a folder path. The moment I removed it, the job ran successfully!

Thanks again.
0
 
TeknikDevAuthor Commented:
I was able to figure out why my package didnt' run in SQL Server Agent.
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, so it was pointing to a path that does not exist on your server. That makes sense. @8080_Diver: I took that it was running slow was actually an indication that the package was NOT running that particular step, i.e., frozen because it could not proceed.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now