Copy external files to different locations

Hi,

I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.

I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.

Many thanks in advanceMark,

this may be a situation where you want to take a step backwards in the requirements definition process and look at the problem from a different context: what is it that your users need? While there are ways to copy data/files from point A to point B using SQL Server, there may be an entirely different approach that would be more in keeping with best practices.

As a first step, I might suggest investigating DTS (Data Transformation Services). I would also look at SQL Reporting Services (though you need a bit of experience setting this up; I wouldn't recommend it for a newbie).

You may also want to consider Access Data Projects (ADP); I don't recommend them for development, but they do provide serviceable reporting tools.

Regardless, focus on the user requirements (not the way they've always dones things, but rather what they really use the information for and how they use it). All that being said, of course you want to meet whatever need in the shortest amount of time possible. Be prepared to take an incremental approach and "lead" your users to a better solution down the road.

Regards,

hmscott|||Thanks for getting back to me.

What I have to do is take data extracts suplied to me by our IT dept and manipulate them, then supply various extracts out of this back to various teams in the company. Because of the way our place works I'd export to another database, zip it, and then move it to a shared (or sometimes protected) location each team has access to.

I used to do this via Access, but now want to up my skills and improve the way I do things.
My theory was:
1. DTS import the data (18 files)
2. Managet the data
3. Export the data
4. Zip the files and move to locations

To be honest I was just hoping it was as simple as a FileCopy but I was just missing it in the ActiveX.

If it's just not feasible yet for me at my skill level I can appreciate that, just thought I'd ask the question.

Many thanks|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'|||I think a light bulb has just come on.
Is it not just the File Transfer Protocol Task.
Just tested it and seems to work fine.|||I had come up with this - while it works on my C drive it doesn't seem to work across networks.

DECLARE @.result1 int
EXEC master..xp_cmdshell 'Copy "C:\ICMS\Fold1\Test.zip" "C:\ICMS\Fold2\Test.zip"'
IF (@.result1 = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'

You can use this method across the network if:
1. You use UNC naming conventions (\\SERVER\Share\folder\file.zip)
2. You SQL Server is running under a service account (not LOCALSYSTEM) and this account has write permissions on the target server.

Still, you might want to consider some other (more scalable/reliable) solutions such as:
1. ADP (already mentioned)
2. Replication to an Access database (never tried it myself, but it's supposed to work)
3. Use DTS to generate the data to the target file (DTS has export connections for Excel, MS Access and other target file types).

Regards,

hmscott

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,

Copy Database wizard issue

Despite the fact that both the package and the model db have the file locations set to e:\data\nnn, the SSIS package is creating / copying the files into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

This is running as a job - could it be a security issue? I don't see any warning / error messages that would indicate an issue.Update...

This is sounding more and more like a bug.

I changed the model db file locations to the c:\ drive (diff location but known access).

no change

I changed the destination file location in the wizard-created SSIS package to the c:\ drive.

no change

No matter what I do, the wizard insists on putting the files into the program files directory listed above.

Both DB's live on the same physical server but different instances. I've combed through the log file and see no errors or warnings of any sort. The account the package is using (again according to the log) is an admin-level account.

I don't see anything on the feedback / bug site related to this.|||Given the deafening silence on this post, I've submitted a bug report. Feedback # is 236131|||

You were right to submit this as a defect. For others reading this thread, the link to the bug is: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=236131

We will investigate the issue and get back to you through the Connect site.

Regards,