?
Solved

copy a file using ssis and sql jobs

Posted on 2011-03-25
4
Medium Priority
?
449 Views
Last Modified: 2012-05-11
I have created an ssis package and then modified it to add a file task at the end.  The file task replaces a file in a mapped directory on a different server.  When I execute the package in Visual Studio it works fine, then when I import the new package into ssis and execute it it works fine.  I created a sql job to run the new package and when I run it, the file is created in the target folder, however, the file does not copy to the other directory.  Is there something special I need to put into the sql job to make the copy part work?  Thanks in advance for any help.
JoMar
0
Comment
Question by:JoMar
[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
  • 2
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35218590
You say "The file task replaces a file in a mapped directory on a different server. " - is this a CMDSHELL copy command?

You say " when I run it, the file is created in the target folder, however, the file does not copy to the other directory. "
so is it the above CMDSHELL copy that is failing? I assume that is because of the userid/login running the package in Visual Studio is different that the SQL Agent executing the job that is running the package. For that you need to create a proxy in my opinion and run it as that proxy - see below

Creating SQL Server Agent Proxies
http://msdn.microsoft.com/en-us/library/ms189064.aspx
0
 

Author Comment

by:JoMar
ID: 35243959
sorry it took so long for me to get back to you.
This is not a command shell.  After the file is created, i inserted a file task to copy the file to a mapped directory, it also replaces the file in the mapped folder.  When I execute the package on as an ssis package, the file is created in the target folder and the file on the mapped drive gets replaced.  When I create a job to run the ssis package, and execute the job, it runs and creates the file on the target drive, however, it doesn't seem to do the copying step.  I will try to create the proxy as you suggested and see if that works.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 35279661
Please ensure the NT account you will be using in the proxy has sufficient permisions at OS level and folders to perform all tasks as the user running it when the copy succeds - check the connection manager for the step that " file task to copy the file to a mapped directory, it also replaces the file in the mapped folder."
0
 

Author Closing Comment

by:JoMar
ID: 35797526
the proxy server issue was stopping me from copying, however, i put the .bat file into a sql job using a cmd line and it is working now.  Thanks for the help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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