Solved

copy a file using ssis and sql jobs

Posted on 2011-03-25
4
431 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
  • 2
  • 2
4 Comments
 
LVL 39

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 39

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 40
Truncate vs Delete 63 107
CROSS APPLY 4 56
Can Unique column have more than one Null? 8 54
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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