February 3, 2004 at 11:05 am
I am moving data from SQL 2K to an Excel spreadsheet. The input to the DTS package is via a sql statement. If I setup a TAB on the spreadsheet with the corresponding columns (and the columns set to the proper format), when DTS auto-maps, all the columns are mapped to varchars, not the data type they should be. If I create an "New Table" in the DTS destintation dialog, the proper mapping is done and everything is great. However, I have more than 1 task in the DTS package with different SQL statements and when I try to define the second (or third) task I get the same issue - wrong mapping in my defined TAB and if I attempt to create a new table, I get "new table already exists". If attempt to rename the "new table" that I created the first time, I lose the mapping orginally created.
Had this working once, but I broke it and don't know why.
Dick
dick rider
February 3, 2004 at 11:38 am
In similar situations I've created and formated my excel sheet and used an active-x tast to look through my recordset and dump data to the xls.
It's takes just a bit to set up since you need to write the cell by cell dump, but the up side is your excel formating stays in place.
Regards,
Matt
February 4, 2004 at 1:24 am
Try this – Don’t manually create the spreadsheet, let SQL server do it. Create the spreadsheet in your package and in your data pump task ‘Destination’ select to create destination table. This should work.
February 4, 2004 at 7:18 am
Thanks for t feedback.
I overlooked the fact that in the "Create" diaglog, I could change the name of the table in the create statement. When I did this (a different name in 3 different tasks, resulting in 3 different tables (tabs on the spreadsheet)), I got exactly what I wanted.
Thanks
dick rider
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply