SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create/Drop Excel table


Create/Drop Excel table

Author
Message
Amy.G
Amy.G
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 300
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
JeridA
JeridA
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 156
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.
Amy.G
Amy.G
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 300
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.
HowardW
HowardW
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12305 Visits: 9893
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.
Amy.G
Amy.G
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 300
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.
JeridA
JeridA
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 156
Is the source data in an Access or SQL Server table?
sql_lock
sql_lock
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4847 Visits: 2022
Are you using x32 or x64?
Amy.G
Amy.G
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 300
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.
JeridA
JeridA
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 156
You need to use the Data Conversion object to convert.
Amy.G
Amy.G
SSC Eights!
SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)SSC Eights! (884 reputation)

Group: General Forum Members
Points: 884 Visits: 300
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search