Solved

copy a file using ssis and sql jobs

Posted on 2011-03-25
4
444 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 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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