Copy file from one server to another using SSIS

Question

Copy file from one server to another using SSIS

CSherwood-465587

Old Hand

Points: 348
More actions
November 20, 2008 at 7:39 am

#194206

Copy the file from one machine to another. Works fine.
However, I then use a stored procedure on the destination machine to import that file into a table. That is failing. All I do to fix it is open the spreadsheet on the destination machine and do a saveas the same file name and then the sp will work. I know it's a permissions thing but can't figure out how to fix it other than manually opening the file and resaving it??????
I'm using the File system task in the SSIS package and OPENROWSET in the sp.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

CSherwood (11/20/2008)
Copy the file from one machine to another. Works fine.
However, I then use a stored procedure on the destination machine to import that file into a table. That is failing. All I do to fix it is open the spreadsheet on the destination machine and do a saveas the same file name and then the sp will work. I know it's a permissions thing but can't figure out how to fix it other than manually opening the file and resaving it??????
I'm using the File system task in the SSIS package and OPENROWSET in the sp.

Are the servers linked??

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

CSherwood-465587 Old Hand Points: 348 More actions · Answer 2

Yes, but I'm not using a linked server in the copy part.

I have done a work around in vbs by copying the file opening it in excel and saving it. Then the sql agent job that does the import works.

I don't understand why the import won't work when either a simple copy is done via ssis or vbs?

Amith Vemuganti Ten Centuries Points: 1350 More actions · Answer 3

I think it runs fine with the SQL Agent job because the job might be using service account to run the process. Have you tried running the SSIS package using the SQL Agent Job?

Thanks,

-Amith Vemuganti

CSherwood-465587 Old Hand Points: 348 More actions · Answer 4

You misread the post. There are two jobs

1. Copy the file: Tried SSIS and VBS Works either way.

2. Import the file: Sql Agent Job. Fails UNLESS I open the file, after the copy, in excel and save as the same filename and then try the import. If I try to import BEFORE resaving the file it will fail.