Export into Excel file from SQL + Named the output files...

Question

Post reply

Export into Excel file from SQL + Named the output files...

cold_blue

SSC Enthusiast

Points: 163
More actions
July 6, 2010 at 4:08 am

#143985

Hi,
Good day. I would need help on the SSIS or SQL.
I need to create a package/stored procedures to export "selective" data into excel from SQL database. Would you mind to guide me on doing this?
I found out that can use "Import and Export wizard" in the Visual Studio, but it seems not practical way in my case. If i use "Import and Export wizard" , what's the next?
Please advise.

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

What are the issues that you are having with the import/export wizard?

cold_blue SSC Enthusiast Points: 163 More actions · Answer 2

Hi Steveb,

It's not able to over-write the existing file when run the package for second time. And, is there a way to rename to file with date-timestamp?

Please advise.

Thank you!

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 3

cold_blue,

use expression as "your file name_Date_Time.xls" (e.g, "Report_20100707_1230.xls) on ExcelFileath property of the excel connection manager

and also set DelayValidation property to true.

RB

cold_blue SSC Enthusiast Points: 163 More actions · Answer 4

Hi RB,

Thanks for your response! Yet, would need your guide on this.

From the Import & Export wizard, the excel connection will be in Data Flow,right?

In the excel destination editor, I do not found any tab which is allowed me to create variable. Do you mind to guide in details?

Thank you!

cold_blue SSC Enthusiast Points: 163 More actions · Answer 5

Hi RB,

I'm able to create variable with the expression you mentioned. Yet, facing error when run the package, do you have any idea on this? Please advise.

Thanks!

TITLE: Microsoft Visual Studio

------------------------------

Error at Data Flow Task [Excel Destination [119]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

Error at Data Flow Task [Excel Destination [119]]: Opening a rowset for "C:\Documents and Settings\Administrator\Desktop\CustomerID_"+Replace(Replace(Replace(Replace((DT_WSTR,50)(getdate()),"-","")," ","_"),":",""),".","_")+".xls" failed. Check that the object exists in the database.

------------------------------

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------

BUTTONS:

OK

------------------------------

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 6

Hi

As the file becomes dynamic now, you need to create structure to hold data before DFT task,

Please refer...Rafael articles on the same...

http://www.rafael-salas.com/2006/12/import-header-line-tables-_116683388696570741.html

and

http://www.rafael-salas.com/2008/03/ssis-and-dynamic-excel-destinations_01.html

charlesbc SSC Journeyman Points: 77 More actions · Answer 7

When working with a single data source and a single data destination it is advisable to use the Import and Export wizard with the SQL Server Management Studio (SSMS). That wizard will generate the SSIS package for you (which you can manage like any other SSIS package).

cold_blue SSC Enthusiast Points: 163 More actions · Answer 8

Hi all,

thank you for your advise!

I'm able to solve it out by using Import & Export wizard ,and with the variable to set the DateTime as part of the filename.

Thank you! 😉