created ssis package for exporting records from sql table to excel.

Question

created ssis package for exporting records from sql table to excel.

Maheshsoft1987

Mr or Mrs. 500

Points: 524
More actions
September 9, 2011 at 12:20 pm

#390375

I have SSIS packages which is work as exporting table records to excel destination. My excel file location is development DBServer and I have deployed packege on live DBServer.

Viewing 15 posts - 1 through 15 (of 15 total)

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

kramaswamy SSCoach Points: 18181 More actions · Answer 1

Pretty open-ended question. If you're asking whether you can deploy an excel file destination to a remote server, then sure, you can, just use the full UNC path when specifying the excel file destination file path.

** edit: though permissions might be a pain to work with. I think you might need to set up some sort of impersonation or proxy account to make it work.

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 2

Thanks for the replying.

I did same thing but I got this error "The Acquire connection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xc0202009".

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I'm having a little trouble on what youi are doing other you want to extract Data From SQ Server to Excel.

Why don't you try doing real quick ussing the SSIS Wizard and see if you get an error?

Start on the Development Server SQL>> Excel.

Then Try it on the Prod >> Dev.

Make sure that the Production Account that you are using to run the package have writes the Share and Excel Folder on the Dev Box.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 4

Thanks for reply.

I have test it before deploying on live server actually.also given full rights to excel folder but still getting error.

kramaswamy SSCoach Points: 18181 More actions · Answer 5

you're really not providing enough details here on what your problem is.

You say you tested it before deploying on live server. You mean you took your package, and had the data source and excel destination both residing on the same server, and it worked, but then when you changed the destination to be a remote server, it failed?

Try deleting the excel destination and connection manager, and re-creating it, using the full UNC path of the remote server, then see if it works.

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 6

sorry, let me clarify.

I meant I have successfully tested it on test DBServer but when I deployed it on live server its getting failed. and about UNC path but obivious, I have mentioned it and given fully access to destination excel file folder.

Thanks for replay.

kramaswamy SSCoach Points: 18181 More actions · Answer 7

Did you try re-creating the excel connector and destination and see if it still fails?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

64 bit or 32bit?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

Did you test using you user Account or the SQL Server Agent Service.

Using Windows Explorer can you navigate to the UNC path and read write to the folder.

You said that you have them full permission. Did you Grant the User Share and NTFS Permissions?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 10

Maheshsoft1987

Mr or Mrs. 500

Points: 524

September 9, 2011 at 2:59 pm

#1379520

Both live and test DBServers are 64bit.

Thanks....

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 11

Yes, I have recreate excel destination connection but still its getting failed.

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 12

I'm using sql agent service.

I have shared excel file folder and given it full right(read,write).

I don't know about NTFS permission. Request you to can you tell about NTFS permission.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

Permissions to be set on the Server that is writing to Excel and the Second Server:

http://www.windowsecurity.com/articles/share-permissions.html

If you have issues log on using the Service Account and read a file and drop a file into the folder.

If it does not work you do not have permissions set up correctly.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Maheshsoft1987 Mr or Mrs. 500 Points: 524 More actions · Answer 14

I have checked it. Its seems like I have read write permission.