June 7, 2006 at 11:46 am
I am exporting data to an excel using a simple sql query in a DTS package.
I want to pass a value to the query at run time.
e.g, select name from record where X = " " ;
I want X to have different values, one at a time.
How to do this using the 'parameters' option in DTS?
or is there another way?
Please let me know.
Thanks,
Krish.
June 8, 2006 at 9:30 am
Couple of different ways to do this, depending on how you are going to executing your DTS job.
First what you need to do is create a global variable (GV) . When you are exporting you records from your database to the spreadsheet use a query instead of a copy table but use the GV instead of copt table or whatnot.
So you use Select col1,col2,col3 etc... FROM mytable WHERE X = ?.
Then click the parameters button and create a global variable or a datatype matching your data you're querying and then set that GV = your first parameter.
Now you just have to set the GV to whatever you need to this can be done either with a dynamic properties task or from the DTSrun cmd line you can pass GV's as well. All depends on how you will be using this.
Heres a website that may be of even more help. http://www.sqldts.com/default.aspx?205
June 14, 2006 at 2:23 pm
How do I declare a global variable?
DECLARE X varchar(10)
Select * from table where table.id = X
is not working.
Thanks.
June 14, 2006 at 2:24 pm
even other data types are not working.
June 15, 2006 at 6:53 am
Declare @X varchar(10); -- Use the amperstand (@)
Select @X = 'test' -- don't forget to set the variable
Select * from table where table.id = @X -- Use the amperstand (@)
June 15, 2006 at 7:18 am
Now that I’ve reread your original post, my question is why do you need to do this in a loop? Why couldn’t you just use something like this…
SELECT col1,col2,col3 etc...
FROM mytable
WHERE col1 IN (‘1stvalue’,’secondvalue’,’thirdvalue’,’etc’) – make your list of values here
ORDER BY col1 --the order by puts it in the order your destination is expecting.
This way your use Set based logic instead of a loop, it will be more efficient and more likely easier to use in your DTS package.
If that won’t work for you and you need to do it in a loop take a look again at the website I gave you before and also think about a few other ways this may be accomplished… 1) Call the package from DTS RUN and pass the GV to it from a second job step or from a Sproc where you canloop over the package to your hearts content, 2) and this is a last resort… you could create a table with you needed values and use some dynamic SQL to create your select statement as such…
1) Try it with a GV…
1. In Enterprise Manager, open DTS designer.
2. Right-click on the white space of the DTS designer and choose Package Properties.
3. Go to the Global Variables tab and create a new global string variable called gv_Myvar, which will hold the data you need in your where statement.
4. In the select query for your Data transformation task use this…
select name from record where ? = ‘’
5. In you sproc, you can the procedure using XP_cmsdhell. The command you will call can be generated via the DTSRUNUI command. Start|Run| DTSRUNUI|OK more information about this can be found here
6. In your sproc you’re going to loop over this command and continue to pass the correct GV
OR
2) Answer 2 not recommended but here for completeness…
DECLARE @Mycounter int,
@Maxcount int,
@sql varchar(8000)
CREATE #tblValues (
ID int,
Value varchar(50)
)
--Code to insert values into temp table… for this example I’ll use X And Y you could use a select statement perhaps… or some other definition…
INSERT INTO #tblValues (Value) Values ('X')
INSERT INTO #tblValues (Value) Values ('Y')
SELECT @Maxcount = SELECT MAX(ID)
FROM # tblValues
SELECT @Mycounter = 1
SELECT @sql = ‘’
WHILE @Mycounter <= @Maxcount
BEGIN
SELECT @sql = @sql + ‘SELECT col1,col2,col3 etc... FROM mytable WHERE col1 = (SELECT Vaule FROM #tblValues WHERE ID = @Mycounter) ’
IF @Mycounter < @Maxcount THEN SLECT @sql = @sql + ‘UNION ALL’
SET @Mycounter = @Mycounter + 1
END
EXEC @sql
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply