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
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 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 (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 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
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 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 (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14085 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
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 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 (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 156
Is the source data in an Access or SQL Server table?
sql_lock
sql_lock
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5277 Visits: 2033
Are you using x32 or x64?
Amy.G
Amy.G
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 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 (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 156
You need to use the Data Conversion object to convert.
Amy.G
Amy.G
Ten Centuries
Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)Ten Centuries (1,000 reputation)

Group: General Forum Members
Points: 1000 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