Solved

BIDS/SSIS Package completes successfully but not executing anything

Posted on 2013-06-05
10
1,824 Views
Last Modified: 2016-02-11
I have a particular bids/ssis package that I have built and then rebuilt from scratch, the first time I built it was on a 2005 box using VS 2005 against a SQL Server 2005 instance and I just rebuilt it using VS Studio 2010 against the same 2005 DB but the job itself runs on a 2012 DB instance.  Hope that second part makes sense.  Basically the code for the BIDS is on the 2012 DB box along with the job but the DB connection in the BIDS points to the 2005 instance.

Anyway, in both situations the ONLY way to get it to work is to open the package and run it in debug mode.  otherwise, when run as a job, it says it completes successfully but nothing happens.  The data never gets inserted into the DB, the file is never moved, etc.  Another interesting thing is that in debug it takes about 2-3 minutes to execute but when the job runs it finishes in about 5-10 seconds.

Anyone have any idea what could be going on and/or how I can fix this.
0
Comment
Question by:cat4larry
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39224179
What is happening is that when you run the package as a job it is failing.  If you were logging the execution of the package you could confirm that.

So the question then becomes why is it failing when run as a job?  And the answer has to do with the fact that when you run it as a job, the SSIS package is running in a different security context compared to you.
0
 

Author Comment

by:cat4larry
ID: 39224306
Hmmm, I believe the logging of the execution of the package is somewhere in the job set up, correct?  I will log it and see what I get back.  As far as the security context, the only place I can see that coming into play is when it grabs the file.  But I have the security set up to all "Everyone" full control over the folder so that doesn't make sense to me.

Anyway, I'll start logging the execution and see if that helps narrow down the issue.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39224317
As far as the security context, the only place I can see that coming into play is when it grabs the file.
Nope.  When you are running it as a SQL Server Agent job, it is running with the startup account used by that service and not your account.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:cat4larry
ID: 39224320
So should I run it under a Proxy Account?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39226416
You can.  Or you can simply make sure the account used by SQL Server Agent has access to the appropriate resources as well as ensure you are not using any mapped drives as you are using a service and no one is logged on.
0
 

Author Comment

by:cat4larry
ID: 39226768
I'm not using a mapped drive per say but I am using a remote location.  I'm accessing that location using it's fully qualified path.  And I'm allowing everyone into the folder.  However, perhaps Everyone only means everyone in that computer's domain?

I'm going to move the file local and see what happens.

BTW, I logged the execution and was getting the following error:

"The application-specific permission settings do not grant Local Activation permission for the COM Server application"

However, I fixed this but still the package didn't run correctly.  I am however not getting any execution errors as far as I can tell.
0
 

Author Comment

by:cat4larry
ID: 39226823
Nope, still not working.  No more execution errors but still not working.  This is actually starting to drive me insane!!!  I have another package which does virtually the same thing (loop over folder, insert data from file, move file, etc) and it is running perfectly.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39227557
Have you even looked to see what account is being used by the SQL Server Agent?  I strongly suspect it is the default Local system account.  If that is the case then it is as simple as changing it to a local user that has a access to all the appropriate local resources or if you are using a remote location use a domain account with the correct permissions.  Trust me, it does not get more difficult than that.
0
 

Author Comment

by:cat4larry
ID: 39230392
Yes, I've done all that and given appropriate access.  However, I will try setting it to use a different user.  But I still think I am doing this correctly at this point.  I'm not getting any execution errors, I've moved the file folder local, I've set the permissions correctly for the execution error I was getting and the job itself is completely successfully.

Thanks for all your help so far!
0
 

Author Comment

by:cat4larry
ID: 39230636
ok, new bit of info.  I decided to store the package in the SSIS Catalog instead (just based on how easy it easy to enable verbose logging if you do).  Anyway, when I browse to the package in SSMS Object Explorer and Execute with Verbose logging it completes successfully, inserts data, moves file, etc.  when I run the job with verbose, I get a line at the end that says:

Foreach Loop Container:Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

Interesting?!?!?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question