SSIS working with VS not with dtexec

Question

SSIS working with VS not with dtexec

Cedders

SSC-Addicted

Points: 459
More actions
December 15, 2009 at 9:18 am

#137675

Hi all,
I created 8 SSIS packages to automate some of my data processes.
They all work fine when run on Visual Studio 2005 and all but one fails when run using dtexec from a query.
All my queries are run from the same server where I created and successfully tested them.
The SSIS package that is failing is moving data from a mapped network drive (a folder in a FTPs server on the same network as the data server) to the server.
I am using a loop to go through some of folders to pick up the name and ensure that it is copied to the proper location.
When I run it with dtexec I have the following:
------------------------------------
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 3:43:53 PM
Warning: 2009-12-15 15:43:54.04
Code: 0x8001C004
Source: Foreach Loop Container
Description: 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.
End Warning
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:43:53 PM
Finished: 3:43:54 PM
Elapsed: 0.968 seconds
NULL
------------------------------------
I also have a "For Each File enumerator is empty" when I run it on VS (sorry don't know how to copy the report) but it works!.
Anyone has an idea why it is happening for one SSIS package only?
Is it because I am copying files from another server?
Any help welcomed.

Viewing 15 posts - 1 through 15 (of 15 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Are you copying or moving the files. If you are moving the files, they are removed from the source location, so if you run it once in VS (BIDS), then run it again using dtexec, there may not be any files to move at that time.

Cedders SSC-Addicted Points: 459 More actions · Answer 2

I am moving the files. But everyday I have a new batch to copy and move, etc...

I am testing the package by reposting test files on the ftp folder.

If run from VB it works fine but dtexec (or SQL job agent) does not!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The output you provided, is that for the successful job or the failed jobs?

Also, when you run the jobs from the SQL agent, do you get a job completed successfully or something else?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Cedders SSC-Addicted Points: 459 More actions · Answer 4

The output provided is for the failed job.

When run from SQL Agent I have a "success" for the job - but my files have not been moved!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Does the account under which SQL Server Agent run have appropriate permissions to the source and destination directories/shares?

Cedders SSC-Addicted Points: 459 More actions · Answer 6

Good point there - how can I check this?

The account I am using is the same one for all tests (SSIS in VS, dtexec through query and SQL Agent)

Cedders SSC-Addicted Points: 459 More actions · Answer 7

Hi,

After working around this I think that the issue is with my connection to the folder on the FTPs server.

The external folder has been mapped on the server where SQL is installed.

I created some batch file and scheduled it and it worked fine overnight.

But I would like to control this with SQL. And when I try to run the batch file with xp_cmdshell I have a "Access id denied' error.

Which means when working with VS or batch files on the server it can see properly the target folder. But this is not the case if I try to run this from a query or a sql agent job.

So the question is how can I set up SQL Agent so that I can interact with the external target folder?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Instead of mapped drives, you should consider using UNC's to access remote files.

Cedders SSC-Addicted Points: 459 More actions · Answer 9

In fact I am using UNC to access the other drive but this is not the issue I guess.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

This is why I suggested UNC's, from your previous post:

The external folder has been mapped on the server where SQL is installed.
I created some batch file and scheduled it and it worked fine overnight.

Have you checked with your system admin regarding NTFS/Share permissions?

Cedders SSC-Addicted Points: 459 More actions · Answer 11

I am going to check! I will let you know if it helps.

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 12

So did you work out if your SQL Agent service account has rights?

To find the agent account, start services.MSC, go find the SQL Agent service, open it and check the logon tab. The default service account (Local\System) does not have rights to external files.

If you have an account in there, you can go find your file and check the effective permissions on it for your SQL Agent logon account

Cedders SSC-Addicted Points: 459 More actions · Answer 13

Nick,

Thanks for that as it seems that SQL Agent is only set up for Local System.

I will now need to figure out what the best way to set this up as I don't want to mess up with the current settings.

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 14

Assuming it is a security issue, you'll need to mess with it to get your SSIS package working.

Normally you would actually create a specific service account and use that. Normally an enterprise has a whole bunch of special accounts which are used for services only.