Not able to write in a specific cell of excel using SSIS

Question

Not able to write in a specific cell of excel using SSIS

reach_saugata

SSC-Addicted

Points: 443
More actions
February 23, 2015 at 9:43 pm

#148790

Hi,
I am trying to write to a specific cell of a excel file using OpenRowset from Variable. Reading dynamically using this works perfect but I am not able to write to an excel file. I am getting the below error:
Opening a rowset for "Sheet1$XX:XX" failed, check that object exists in the database.
After doing some research I have set DelayValidation to True for the connection manager, but to no avail.
The other property changes I have made are:
1) Run64bitRuntime to False (since I have 32 bit excel installed it seems)
2) ValidateExternal Metadata to False for Excel destination
Any help is highly appreciated.
Regards,
Saugata Mukherjee.

Viewing 14 posts - 1 through 13 (of 13 total)

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

SQL.AP Hall of Fame Points: 3244 More actions · Answer 1

Check you Excel if it really has Sheet1 present or not. Also try configuring this Excel as a source, may be the sheet you are referring is not available.

You can refer below post, see if it can help -

http://stackoverflow.com/questions/12415838/ssis-excel-to-sql-import-first-6-rows-of-the-file-contains-header-related-inf

____________________________________________________________

AP

reach_saugata SSC-Addicted Points: 443 More actions · Answer 2

Hi,

I am creating a new Excel file, so the output file does not exist. That is why I have set DelayValidation to True for the connection manager. That should work, right?

Regards,

Saugata Mukherjee.

SQL.AP Hall of Fame Points: 3244 More actions · Answer 3

No, in case of Excel files, a destination file should be present at the time of loading. SSIS will not create an Excel file dynamically.

In cases of Excels, generally we keep one template Excel file (having same format as your destination Excel) on a shared drive.

Then in the package before loading, copy this template to your working location. Load data into it and afterwards rename it to your desired file name.

You can refer below post:

http://www.codeproject.com/Articles/402958/SSIS-Package-Export-Data-from-Database-Daily-to-Ne

____________________________________________________________

AP

Lempster SSCoach Points: 15651 More actions · Answer 4

anshulparmar (2/23/2015)
No, in case of Excel files, a destination file should be present at the time of loading. SSIS will not create an Excel file dynamically.

That's not correct; it is possible to create an Excel file dynamically, but it requires the use of OPENQUERY...CREATE TABLE.

Regards

Lempster

SQL.AP Hall of Fame Points: 3244 More actions · Answer 5

Lempster (2/24/2015)
anshulparmar (2/23/2015)
No, in case of Excel files, a destination file should be present at the time of loading. SSIS will not create an Excel file dynamically.
That's not correct; it is possible to create an Excel file dynamically, but it requires the use of OPENQUERY...CREATE TABLE.
Regards
Lempster

What you are talking about is creating an Excel sheet and table on the go then load data into it.

So, Whatever the case be, but while loading Excel file should be present. .

____________________________________________________________

AP

Lempster SSCoach Points: 15651 More actions · Answer 6

Yes, I misunderstood what you were saying, sorry. Loading data into a destination that does not exist is clearly not going to work! 😉

reach_saugata SSC-Addicted Points: 443 More actions · Answer 7

Hi,

Thanks for your help.

helped a lot.

Now, in the above site they have a header (lets say A1:D1) in the template file and the program is trying to insert data from the first row itself. Now if I want to insert data in the same sheet to a different range (say A15:D23) using OpenRowsSet, I get the same error: [Excel Destination [2]] Error: Opening a rowset for "Sheet1$A15:D23" failed. Check that the object exists in the database.

It seems SSIS expects at least one non blank row in the selected range while inserting data. So, now my problem is, how to insert data to a completely blank excel sheet.

Is there a way to do that? If not, then can anybody suggest a work around to achieve this?

Regards,

Saugata Mukherjee

SQL.AP Hall of Fame Points: 3244 More actions · Answer 8

Can you share your sample package and file?

____________________________________________________________

AP

reach_saugata SSC-Addicted Points: 443 More actions · Answer 9

I have attached the Input and output file and the package. I want to read the records from Sheet1$A3:D6 of Read_file - copy.xls. And Write it to Sheet1$A10:D14 (decided during run time and using variable) of Write_File.xls

Since I am not sure where I want to write beforehand I am not able to provide header in place before executing the SSIS package. And it seems that SSIS need data in at least one of the cells within Sheet1$A10:D14 of output file.

Let me know if you need any other information.

Thanks in advance.

reach_saugata SSC-Addicted Points: 443 More actions · Answer 10

I have attached the Input and output file and the package. I want to read the records from Sheet1$A3:D6 of Read_file - copy.xls. And Write it to Sheet1$A10:D14 (decided during run time and using variable) of Write_File.xls

Since I am not sure where I want to write beforehand I am not able to provide header in place before executing the SSIS package. And it seems that SSIS need data in at least one of the cells within Sheet1$A10:D14 of output file.

Let me know if you need any other information.

Thanks in advance.

SQL.AP Hall of Fame Points: 3244 More actions · Answer 11

Unfortunately I was unable to open your package however I tried executing your files and yes, I succeeded.

I was able to load data in Excel from Sheet1$A3:D6 to write file Sheet1$A10:D14.

Just make sure you are configuring it right.

Try this -

1. Create a new package

2. Create a DFT

3. Take Excel source and point it to your read file. right click excel source task and click properties, then update openrowset to Sheet1$A3:D6.

4. Refresh metadata of your excel source task.

5. Take Excel destination and point it to your write file (write file should be present at location). Right click excel destination task and click properties, then update openrowset to Sheet1$A10:D14.

6. Refresh metadata of Excel destination task.

7. Also, right click your PROJECT , go to properties then Debugging and update Run64BitRuntime to 'False'

8. Execute your package, it will load data in write file starting from A3 till D6.

____________________________________________________________

AP

reach_saugata SSC-Addicted Points: 443 More actions · Answer 12

Hi,

First of all thanks. Your solution worked for the first time (with a little caveat. I can only populate 3 fields and not all 4 input fields though I have given reference from A to D, which is 4 columns).

Then I have tried it with a newly created empty excel file, followed the same process. And it is not working, getting the same problem. So, essentially the same package works for one file (Write_file.xls) but when I point the excel destination to another file (Writeq.xls, which is basically another empty xls file), it stops working.

I am really confused, and probably doing some silly mistake. I am uploading the package/project as well as both the files. Hopefully this time you will be able to see this package where I have done two separate streams, one working and the other is not with no apparent reason.

Also you can see the two output files which looks to me the same (but probably something is different. Clearly one file is slightly larger than the other.), but producing different results.

Will you be so kind and again take a look into this?

Regards,

Saugata.

SQL.AP Hall of Fame Points: 3244 More actions · Answer 13

If you are changing files, you need to refresh metadata in the destination/source.

You are getting errors only because it is not correctly configured and always make sure the destination and source files are available at the time of loading.

Also, if you are using any new files. better create a new DFT rather than updating the previous one. It will help you to avoid any un-neccessary errors.

____________________________________________________________

AP