May 20, 2008 at 11:50 am
I have problem with SSIS Packages which seem very similar to many others but I may have a unique twist.
I am using SSIS to move files from a server outside our local network and move these files onto a server within our network. In my SSIS Package I use FOR EACH LOOPs to iterate through the files on the outside server, however, everytime I execute the package using SQL Agent it logs that there were no files in the folder. So the package executes and does not fail but it really doesn't work; there are files there. It just doesn't see the files.
When I run this package through Visual Studio, it works just fine. It see the files and moves the files. It just won't work when running through SQL Agent as a job.
After reading and following many suggestions that this is a permissions issue I have tried everything people have suggested with no success. I am convinced that the issue has to do with the fact that my source files are in an outside stand-alone computer and not in my network.
Does anyone have any suggestions on how I can gain access to these files outside my network using a SQL Agent Job?
Thanks in Advance
Greg
May 20, 2008 at 12:04 pm
Try adding some row count variables and determing what the SSIS package is seeing vs. what the SQL Agent job might be reporting. This should help you determine if the server that is executing the packages is seeing the folder location or not. Also, if this is an option, you might try logging into the server running the packages from within BIDS there.
Good Luck!
Ben Sullins
bensullins.com
Beer is my primary key...
May 20, 2008 at 12:40 pm
I've added logging to my SSIS Package and it shows it entering the package and the FOR EACH LOOP is logging this:
OnWarning,TIF FEL OlderEstates FEL,5/20/2008 2:32:33 PM,5/20/2008 2:32:33 PM,-2147368956,0x,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 folder it is checking is not empty.
I appologize for not adding a variable to check row counts but I am new to SSIS and am not sure how to do that. The Package was created on the same machine as the SQL Agent that is trying to execute the job.
Thanks
July 31, 2008 at 9:09 am
I am having a similar problem with files inside my network located in the same share as the rest of my ssis package files. I can run the package from the server just fine. As soon as I schedule a job I get no files in directory message from my package logs. Very frustrating.
I doubt if it's a security issue. I would think maybe there is a reliance on the package configuration to get the filter or something, so it's not able to read the directory for what I want. That's a shot in the dark. Can someone please explain how the package configuration file interworks with the package. Are the terms overriding or inheriting applicable to ssis package data and config data?
August 1, 2008 at 8:05 pm
My friends DBA, on this subject I wish to inform you that I have no kind of trouble,using the same environment of folders with SSIS, this tool has been working with satisfactory results through scheduled jobs,if you need help in this case maybe I can send the script of my jobs and dtsx for you appreciate, I know this tool pretty good and I will be help you.Attention for syntax inside the job, where has the correct path of dtsx.Awaitting your prompt response on this matter.
Deg
August 3, 2008 at 9:44 pm
Try running the package interactively ON the physical server (if you have access) using DTSUtil so you can see what's happening in the context of the server. This won't eliminate any security issues because it's still running under the context of 'you' but it's a start. If, when you log onto the server, you can log on using the account the SQL Agent or the job is running under then even better because you can see if there is a security problem.
You don't mention how the location is populated in the task... is it hardcoded? Is it from an expression? Is it from a package configuration?
Kristen Hodges
b. http://bi-tch.blogspot.com
August 3, 2008 at 9:47 pm
Brian - it sounds like your problem is different to gregory's so it might be appropriate to log your own issue rather than hijacking his 🙂
As a quick start, it may help you to understand that package configurations are populated PRIOR to package initialisation. It's the very first thing that occurs. As to 'file interchange' - package configs are not specific to file i/o in any way - package configurations are values stored external to the SSIS package which can be used to update properties, variable values and connections at runtime.
Kristen Hodges
b. http://bi-tch.blogspot.com
August 3, 2008 at 9:53 pm
Gregory,
I just note that your post says the files are not on the 'local network'. Are the files in a different domain? or on the internet? Can you clarify what you mean by this?
Kristen Hodges
b. http://bi-tch.blogspot.com
August 4, 2008 at 7:03 am
Thanks everyone for posts regarding this. Due to the immediacy of this issue I had to punt and go to a work around.
The FTP server is outside of our domain in a DMZ zone. I have access to it directly through \\IP_Address however when deploying my SSIS using this fashion I get the reported problem.
To resolve this I had to modify my code to actually use FTP to pull the files down to a local serevr. This has limited my ability to do file manipulation in place until the files are located on a local server. This added several steps to my SSIS but my work around is working.
Thanks for your suggestions.
Gregory
August 4, 2008 at 5:42 pm
Yep that explains it then. Your local server will not have access to a server in the DMZ unless it's explicitly granted. You'd need to talk to whoever from your organisation manages ISA (or suchlike).
Kristen Hodges
b. http://bi-tch.blogspot.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply