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 |
|
SQLfriends
Starting Member
12 Posts |
 Posted - 2002-11-01 : 11:12:29
|
| I need to create a package that takes table/view name and file name as parameters, dumps the table data into a Excel Spreadsheet and then names the spreadsheet based on the parameter input. The following is my thought:Global input parameters: tableName, fileNameGlobal input parameters: tableResultSets ActiveX Scripts Tasks -?-> Execute SQL Task ---> Excel 97-2000(Get the table recordset) (create the table (Transform Data dynamically) Task)From the tableName parameter, I will make a query to get the result set and assign it to a global output variable. Can I copy/transfer the recordset dynamically to an "Execute SQL Task"? Then I will be able to use the Transform Data Task to export the table data to the Excel Spreadsheet directly. If that is not possible, I probably need to write more scripts to create Excel worksheet object, create mapping columns and then copy the result sets to the worksheet. Are there any articles or sample code available on the web? I found a lot of articles covering how to export Excel Spreadsheets to SQL server, not the other way around.Thanks. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-01 : 12:18:37
|
| I know there are ways to do that with DTS, unfortunately I haven't done it that way myself. However, you can do this pretty easily using bcp. It can be completely dynamic as far as the query, format, and output file names are concerned. You can output the file in CSV or tab-delimited format, give it an .XLS extension, and Excel will automatically convert it when it's opened. There's an example of bcp here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21175There is more detail in Books Online on bcp and its options, in case DTS gets to be too complicated a solution for you. |
 |
|
|
SQLfriends
Starting Member
12 Posts |
Posted - 2002-11-01 : 16:36:39
|
Thanks for your idea. I encounter a BCP error while executing the BCP command. Since the error is not directly related to the original topic that I posted, that is why I put up a new topic.quote:
I know there are ways to do that with DTS, unfortunately I haven't done it that way myself. However, you can do this pretty easily using bcp. It can be completely dynamic as far as the query, format, and output file names are concerned. You can output the file in CSV or tab-delimited format, give it an .XLS extension, and Excel will automatically convert it when it's opened. There's an example of bcp here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21175There is more detail in Books Online on bcp and its options, in case DTS gets to be too complicated a solution for you.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-01 : 16:59:59
|
Don't worry about drifting a little off topic from the original thread, that happens from time to time on SQL Team.  You can try reinstalling the client tools for SQL Server, it should include the missing DLL that caused the error. |
|
|
|
|
|
|
|
|
Spreadsheets