Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Create/Drop Excel table Expand / Collapse
Author
Message
Posted Tuesday, March 08, 2011 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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
Posted Tuesday, March 08, 2011 9:53 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 19, Visits: 149
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
Posted Tuesday, March 08, 2011 9:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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
Posted Tuesday, March 08, 2011 10:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 1,207, Visits: 9,346
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
Posted Tuesday, March 08, 2011 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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
Posted Tuesday, March 08, 2011 11:35 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 19, Visits: 149
Is the source data in an Access or SQL Server table?
Post #1075008
Posted Tuesday, March 08, 2011 11:43 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:43 AM
Points: 1,487, Visits: 1,688
Are you using x32 or x64?
Post #1075013
Posted Tuesday, March 08, 2011 11:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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
Posted Tuesday, March 08, 2011 12:02 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 19, Visits: 149
You need to use the Data Conversion object to convert.
Post #1075031
Posted Tuesday, March 08, 2011 12:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse