Create/Drop Excel table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Create/Drop Excel table

22 posts, Page 1 of 3

1 2 3 »»»

Create/Drop Excel table

Rate Topic

Display Mode

Topic Options

Author

Message

Amy.G

Posted Tuesday, March 08, 2011 8:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:23 AM
Points: 96, Visits: 266

We recently updated to SQL Server 2008. After converting all the SSIS packages, one feature no longer works. This is using an Execute SQL Task to create or drop an Excel file in a folder. This is a handy feature I would rather not lose.

Has anyone else had this issue and found a work-around?

Thanks in advance for help,

Amy

Post #1074874

JeridA

Posted Tuesday, March 08, 2011 9:53 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:59 PM
Points: 19, Visits: 133

In SQL Server 2005 we use a File System Task to creat the excel file, then an OLE DB datasource to feed the results of our query to an Excel Destination object. This method should work in 2008, but I haven't tested it.

Forgot one piece of info, we have a blank excel template setup that we copy with the file system object, this way all of the field names are setup.

Post #1074937

Amy.G

Posted Tuesday, March 08, 2011 9:58 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:23 AM
Points: 96, Visits: 266

I'm using a Microsoft.Jet.OLEDB.4.0 as the connection manager to move the data into an Excel file, but now when I chose the file, I get this:

TITLE: Connection Manager
------------------------------

Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\Temp\Test.xls'.

But when I use an Excel Desination, I keep getting a message saying "cannot convert btw unicode and non-unicode string..." this persists whether or not I use a data converter.

I'm obviously missing something.

Post #1074941

HowardW

Posted Tuesday, March 08, 2011 10:06 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 1,092, Visits: 7,931

This doesn't have anything to do with the move to SQL Server 2008. You need to convert any non-unicode strings to unicode before they reach the destination in your data flow (if it doesn't work even though you've used a data conversion step, check that you're definitely mapping the converted strings into the destination rather than the original ones as it won't replace them).

A good starting place would be to use the import/export wizard in SQL Server Management Studio to export some data, chosing Excel as your destination and then choose to save to an SSIS package, so you can see how the table creation and conversion works.

Post #1074946

Amy.G

Posted Tuesday, March 08, 2011 10:40 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:23 AM
Points: 96, Visits: 266

HowardW (3/8/2011)

A good starting place would be to use the import/export wizard in SQL Server Management Studio to export some data, chosing Excel as your destination and then choose to save to an SSIS package, so you can see how the table creation and conversion works.

I have now tried this, and the convertion the wizard used to successfully move the data still has an error in SSIS. I have tried all data types from the data conversion transformation, and the same error persists.

Post #1074966

JeridA

Posted Tuesday, March 08, 2011 11:35 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:59 PM
Points: 19, Visits: 133

Is the source data in an Access or SQL Server table?

Post #1075008

sql_lock

Posted Tuesday, March 08, 2011 11:43 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 4:14 AM
Points: 1,297, Visits: 1,551

Are you using x32 or x64?

Post #1075013

Amy.G

Posted Tuesday, March 08, 2011 11:50 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:23 AM
Points: 96, Visits: 266

JeridA (3/8/2011)

Is the source data in an Access or SQL Server table?

The source data is a SQL Server Table. The destination is an Excel file.

OK, I have now figured out I needed to put "Excel 8.0" in the Extended properties for the OLE DB Jet 4.0 connection to work. One down.

I am still getting the unicode convertion error. I now have a derived column tool to convert, and the expression (DT_WSTR,2)"ID" worked and I don't have an error for that column, but the similar (DT_WSTR,20)"Name" did not work, and still have the "Name" and "NAME" cannot convert btw....

And I use 32 bit.

Post #1075019

JeridA

Posted Tuesday, March 08, 2011 12:02 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 6:59 PM
Points: 19, Visits: 133

You need to use the Data Conversion object to convert.

Post #1075031

Amy.G

Posted Tuesday, March 08, 2011 12:15 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:23 AM
Points: 96, Visits: 266

JeridA (3/8/2011)

You need to use the Data Conversion object to convert.

Yes, I went back to that, and saw that I had to map "copy of [column]" to output. Very happy that is over.

But now the Drop Table command isn't working.

Post #1075041

« Prev Topic | Next Topic »

22 posts, Page 1 of 3

1 2 3 »»»

Permissions