Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

copy a file using ssis and sql jobs

Posted on 2011-03-25
4
Medium Priority
?
454 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

618 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