Why adding Data in file name works in Flat File destination but not in Excel Destination?

Question

Why adding Data in file name works in Flat File destination but not in Excel Destination?

Munabhai

Default port

Points: 1445
More actions
August 13, 2012 at 4:03 pm

#149446

I am loading data from SQL Server to Flat File and add date at end of filename. It works perfectly. Same way I try, taking Excel as Destination and change replace ".csv" with ".xsl" it is throwing an error. Do I need to pay attention in Excel Destination.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Munabhai (8/13/2012)
I am loading data from SQL Server to Flat File and add date at end of filename. It works perfectly. Same way I try, taking Excel as Destination and change replace ".csv" with ".xsl" it is throwing an error. Do I need to pay attention in Excel Destination.

First, Excel can open a file with the extension .csv. Second, if I remember correctly, the extension for Excel files is .xls (or for newer versions, .xlsx).

MrReddy Ten Centuries Points: 1083 More actions · Answer 2

Simply replacing the file extension won't work with excel.

Can you please post here how you are adding the date column at the end of the file name ,are you using the file system task with rename or how you are doing that??

and can you post the error that you are getting..

Munabhai Default port Points: 1445 More actions · Answer 3

Lynn, Yes you are correct I did open .csv file in Excel the problem is some of the data have , in middle so it is creating new column in .csv file. That's the main problem. Second one is xsl is typo.

Munabhai Default port Points: 1445 More actions · Answer 4

First I create a package level, String Data type variable.

I have data flow task, Data is retrieving from OLEDB Source to Excel Destination. For simplicity I made source table all int data type so that I do not have to convert data.

I change the property of variable as EvaluateAsExpression – True, Expression I put "c:\\test" + (DT_WSTR, 20) (DT_DBDATE) GETDATE() + ".xls" which mean c:\test2012-08-14.xls

On Excel destination, just give one dummy location in c drive. And did mapping.

Click on Excel connection manager in connection manager, I went to property click on Expressions. Under Property I choose connectionString, click on expression, expand the Variables drag the variable in the Expression box. Click on Evaluated expression. It did show same path c:\test2012-08-14.xls

After doing all this, Excel destination still show red cross sign. Error says:

“NonFatal errors occurred while saving the package:

Error at Package [Connectin manager “Excel connection Manager”]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error at Package: The result of the expression “@@User::test1]” on property “connectionString” cannot be written to the property. The expression was evaluated, but cannot be set on the property.”

I did same thing for .csv file no error, it seems something need to be done in Excel. I did google, I donot fine any solution.

MrReddy Ten Centuries Points: 1083 More actions · Answer 5

Instead of connection string in excel connection manager properties use excel file path.

I faced the same error and got solved with this change. I hope it will work for you.

Munabhai Default port Points: 1445 More actions · Answer 6

I did that, it throw next error some thing like

"Error at Data flow task [Excel Destination [30]]: SSIS eRROR cODE dts_E_oledberror. An OLE DB error has occured. Error code: 0x80040E37.

Error at Data Flow Task [Excel Destination [30]]: Opening a rowset for "Excel_Destination" failed. check that the object exists in the database.

.............

...

MrReddy Ten Centuries Points: 1083 More actions · Answer 7

It seems the error is because of excel file name, give the name of the excel file as test.xls as your dynamic file name is test2012-08-13.xls. Try with this.

Munabhai Default port Points: 1445 More actions · Answer 8

The way you suggest will make file name static. For Static I do not have to go all this long way. In SSIS, before loading data in Excel it look Excel file exist or not. In my scenario, I do not have file exist before SSIS PACKAGE run because my file is dynamic name.

Where as in .csv it is working fine becasue it donot need file exist while loading data in flat file.

MrReddy Ten Centuries Points: 1083 More actions · Answer 9

I faced the same issue as you are facing now. You can get the csv or txt file names dynamically because they don't need the column names in the beginning,writing the data to the new file or the old file doesn't effect but for the excel you have to mention the column names before you load the data.If it has to change the file name the new file has to contain the column names. And here why am asking you to name the file name as test.xls because you want the dynamic excel file name as test_(date).xls and here the date field is dynamic as it will change everyday, if you give any other name other than test.xls it won't work for you. I did a lot of work for dynamic excel connection manager but nothing work for me. So finally I came up with this solution and is greatly solved my issue. My intention is not to say it is impossible but dealing with excel is definitely troublesome.