July 17, 2016 at 8:12 pm
I have a very simple SSIS package I created in VS2015 whose first step is a FileSystem task to rename a file. It executes fine in debug mode. I deployed the project to the Integration Services catalog, then created a SQL Agent Job to execute the package. But the SQL Job fails because it "could not find file \\SCMISC\testdata.txt". (Yes, I did change the name back to its original). I've tried everything -- deploying it as a package and a project in the Integration Services catalog, and fiddling with the Package sources in the Job Step. It fails every time.
What am I doing wrong?
--SSIS rookie
July 18, 2016 at 12:46 am
dhb (7/17/2016)
I have a very simple SSIS package I created in VS2015 whose first step is a FileSystem task to rename a file. It executes fine in debug mode. I deployed the project to the Integration Services catalog, then created a SQL Agent Job to execute the package. But the SQL Job fails because it "could not find file \\SCMISC\testdata.txt". (Yes, I did change the name back to its original). I've tried everything -- deploying it as a package and a project in the Integration Services catalog, and fiddling with the Package sources in the Job Step. It fails every time.What am I doing wrong?
--SSIS rookie
Quick thought, check the credentials that the package is running under on the server, certainly those are different than your credentials during debug, suspect that the server credentials do not have access to that part of the file system.
😎
July 18, 2016 at 5:47 am
Credentials are OK. I have other SQL jobs that read files (not write) from the same server and directory.
July 18, 2016 at 6:26 am
The error would suggest that the file could not be found, rather than the permissions being denied. The name of your file, however, doesn't look correct, there is no share defined. The file path should be made up of the Servername, share/filepath, Filename. you only have a server and file name in your error message. I would expect to see something more like:
File '\\FileServer\Publicfiles\testfile.txt' not found". Can you confirm that the filepath is indeed correct. That is what your error message is telling you is wrong.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 18, 2016 at 6:52 am
Thom A (7/18/2016)
The error would suggest that the file could not be found, rather than the permissions being denied. The name of your file, however, doesn't look correct, there is no share defined. The file path should be made up of the Servername, share/filepath, Filename. you only have a server and file name in your error message. I would expect to see something more like:File '\\FileServer\Publicfiles\testfile.txt' not found". Can you confirm that the filepath is indeed correct. That is what your error message is telling you is wrong.
Yes, I used the fully-formed filename: \\SCMISC\ShareName\testdata.txt (I disguised the actual sharename for this discussion). If it wasn't a valid filename, it wouldn't have passed the debug test.
July 20, 2016 at 1:42 pm
Never did get the SSIS package to see the text file. Even tried running as a proxy account. I spent 3 days running down blind alleys trying to get this to work.
I finally worked around SSIS by using xp_CmdShell in a stored procedure to import the text file and then move it to an archive when finished. It took about 10 minutes to write and test.
July 21, 2016 at 1:56 am
dhb (7/20/2016)
I finally worked around SSIS by using xp_CmdShell in a stored procedure to import the text file and then move it to an archive when finished. It took about 10 minutes to write and test.
Although this is a solution, I highly do not recommend having xp_cmdshell enabled on a live, or even development, server. There are a lot of security implications.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 21, 2016 at 7:07 am
Thom A (7/21/2016)
dhb (7/20/2016)
I finally worked around SSIS by using xp_CmdShell in a stored procedure to import the text file and then move it to an archive when finished. It took about 10 minutes to write and test.Although this is a solution, I highly do not recommend having xp_cmdshell enabled on a live, or even development, server. There are a lot of security implications.
Like what?
July 21, 2016 at 9:52 am
Hmmm, OK. What about Bulk Insert as an alternative to SSIS and xp_CmdShell?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply