Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
twl55
Starting Member
19 Posts |
 Posted - 2011-09-24 : 16:01:39
|
| I am attempting to export a view from SQL Server 2005 to and Excel 97-2003 spreadsheet. The view has 2 integer and 1 date columns, as well as a number of text columns.I created a SSIS package to do this using the Import and Export Wizard. If I output to the default sheet, I can set the datatypes on the 3 fields and they copy into Excel as the integer and datetime datatypes.What I'd really like to do is have SSIS copy to an existing sheet where a company logo and other information sits in the first few rows. I can construct a package to perform this copy and the data shows up onto the existing sheet, but the 3 fields in question are all text fields.Any help would be appreciated.Thanks in advancetwl55 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-24 : 23:39:49
|
| Not sure if that's possible with SSIS unless you use a script task and script out the Excel object model.Might be easier to pump the raw data to Sheet 2, and have Sheet 1 reference it. |
 |
|
|
twl55
Starting Member
19 Posts |
Posted - 2011-09-25 : 08:43:05
|
| I know this isn't the forum for this, but how do have a large group of cells reference a large group of cells on another sheet. I know how to do it with a single cell. I'd like from A5 to the the of the sheet reference from A2 to the end of another sheet.Thankstwl55 |
|
|
|
twl55
Starting Member
19 Posts |
Posted - 2011-09-25 : 13:05:47
|
| Further investigation shows me that SSIS is assuming that since the existing sheet I need to copy into has text in the first couple cells of each column, it is converting all the data in each column to text.So, my question becomes, does anyone know how to use SSIS to output into Excel using the data type of the data even if there are text columns headers for each column in the first few rows?Thankstwl55 |
|
|
|
twl55
Starting Member
19 Posts |
Posted - 2011-10-03 : 16:34:25
|
| I figured out a solution, sort of. It seems the SSIS export process to Excel uses the datatype of the last data value in the column under which the exported data is going to be placed to determine the datatype of the imported data.So, since I am placing data underneath text column headers, all the data is text. If I have a column header of "Qty" and place a numeric 20 under that, the data comes into Excel as numeric data. I can even hide the cell with the 20, and it still comes in as numeric. Sort of a fix.But, if I do the same with date columns by setting the cell under the heading to a date, data comes in as dates. If I hid the cell with the explicit date, the data comes in as text. I've not been able to crack this one.twl55 |
|
|
|
|
|
|
|
|
Export numbers to existing Excel spreadsheet