Solved

copy a file using ssis and sql jobs

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

729 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