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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-01-04 : 08:43:05
|
| robbie writes "I have created a DTS package that will happily import a named range from an Excel spreadsheet and append it to a table. The problem is that I want to do this multiple times, each time accessing a different sheet in the Excel workbook. At the moment I have a package global variable that is accessed by the transform script, and whose value I have to change each time. I also have to change the FROM field in the SQL statement.Here's the SQL statement in the "Transform Data Task Properties" dialog (Source tab; I've condensed some fields to [...] to save space here:select `Descriptor`,`Descriptor1`,`Data entry`,`Units/year`,[...],`Mandate` from `West_Coast_ecolIn`I'd thought that I might be able to use an EXEC statement here, and use that to combine to strings, the second one being a variable, but it doesn't let me. I tried something like this:exec "select 'A','B' from " + @myvarand it gives an error.Ideally I would be able to make a list somewhere (in a table?) of strings that can be iterated through and the package run with the new variable values. As it is, I have to a hell of a lot of pointing and clicking, copying and pasting.Any ideas?I'm running Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) and XP SP1.Cheers" |
|
|
|
|
|
|
|
Auto import from multiple sources whose names are defined in a list