x

SSIS FST moving files from remote drive to local fails in SQL Agent

Hi everyone,

I have been racking my brain with this one all morning.

My problem is that I have an SSIS package which moves files from a remote folder to my local server using a file-system-task.

The folder is mapped using NET USE and I can access the files fine. When I manually run the SSIS package in BIDS all is ok and the files move to the server. When I schedule this package it succeeds however no files are moved (There are no errors in the job history).

I am using an SSIS proxy with domain credentials for the server not the remote folder. The remote drive has different domain details (I think this might be the problem).

The package is deployed using SSIS catalog and using SS2012.

Any help would be really appreciated.

more ▼

asked Feb 05, 2014 at 10:40 AM in Default

avatar image

@SQLShark
3.9k 6 11 14

I am getting this warning:

Loop over the files move: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.

The file pattern is *.* and the directory has files in it.

Feb 05, 2014 at 11:50 AM @SQLShark

are you trying to move or copy the files? Are the files marked as read only?

Feb 05, 2014 at 03:01 PM Fatherjack ♦♦

No not read-only.

Feb 05, 2014 at 03:14 PM @SQLShark
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Right. Proxies were failing for me however this link solved it for me.

http://sqlserverselect.blogspot.co.uk/2012/05/ssis-accessing-network-resources-on.html

I created an "Execute Process Task" to map the drive before pulling the files.

I don't think this is a long term solution however it fixes my immediate issue.

Thanks for the help all.

more ▼

answered Feb 07, 2014 at 09:39 AM

avatar image

@SQLShark
3.9k 6 11 14

(comments are locked)
10|1200 characters needed characters left

Can you try a different source (on the domain, where you can be 100% certain that permissions are correct) and see if you have the same issue? Alternately, can you temporarily alter the security on the remote location to be EVERYONE allowed and test? This will show if the location and it's applied security settings are the problem.

more ▼

answered Feb 05, 2014 at 02:58 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

Hello.

Tried with a different folder on the same remote drive. BIDS - all worked fine. SQL-Agent - Succeeded but no files were moved.

Tried on a local path (on the same server). Worked under both situations.

Wrote a C# script to move the files. BIDS - all worked fine. SQL-Agent - Failed. Couldn't open the location.

I cant change the permissions on the remote drive unfortunately as it is hosted by a software provider. I have asked them for FTP access if I cant get this working. Thanks

Feb 05, 2014 at 03:12 PM @SQLShark
(comments are locked)
10|1200 characters needed characters left

Can you change the proxy account with your credentials(as you are able to manually run the package) and see whether it is working ? Try to create another domain account (which you have mentioned having access to drive) as a proxy and try ? I feel that there is some issue with the proxy account you have configured for that path.

more ▼

answered Feb 06, 2014 at 04:16 AM

avatar image

Venkataraman
1.4k 1 3 6

Hi Ven. Sorry for the delay.

I will have a play with the proxy. I will get back to you.

Feb 07, 2014 at 08:57 AM @SQLShark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1231
x18
x9
x6

asked: Feb 05, 2014 at 10:40 AM

Seen: 2853 times

Last Updated: Feb 07, 2014 at 09:39 AM

Copyright 2018 Redgate Software. Privacy Policy