March 8, 2011 at 8:55 am
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
March 8, 2011 at 9:53 am
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.
March 8, 2011 at 9:58 am
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.
March 8, 2011 at 10:06 am
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.
March 8, 2011 at 10:40 am
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.
March 8, 2011 at 11:35 am
Is the source data in an Access or SQL Server table?
March 8, 2011 at 11:43 am
Are you using x32 or x64?
March 8, 2011 at 11:50 am
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.
March 8, 2011 at 12:02 pm
You need to use the Data Conversion object to convert.
March 8, 2011 at 12:15 pm
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.
March 8, 2011 at 12:34 pm
Little unsure what you're doing... drop a SQL table?
March 8, 2011 at 12:41 pm
JeridA (3/8/2011)
Little unsure what you're doing... drop a SQL table?
Jerid,
I am using an Execute Sql Task (which is originally what I posted in the forum for help before being side tracked by unicode transformations) to drop and create an excel file. I am getting an error:
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
March 8, 2011 at 12:53 pm
You can use the File System task to drop the Excel File.
March 8, 2011 at 1:35 pm
Here is how I do it in one of my SSIS packages.
1) Fig 1 - Delete the exisiting excel file
2) Fig 1 - Copy an empty Excel template into place (Detail in fig 3)
3) Fig 2 - Query the table
4) Fig 2 - Extract to the Excel file
5) Fig 1 - Then I email the excel file
March 8, 2011 at 2:23 pm
JeridA (3/8/2011)
quote]
I feel just a little ridiculous for not having realized this. My mind was set around having to use a SQL task and just focused on figuring this out. But now, I have an excel template, table that regenerates and a huge load off my mind.
Thank you SO much Jerid.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply