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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy