Looping through results to use as parameters

Question

Looping through results to use as parameters

Ged Aitchison

Valued Member

Points: 54
More actions
April 18, 2007 at 5:45 pm

#68912

Ok, I have a package that at them moment asks for a couple of parameters via input box and then gets results and puts those results into files with the filenames based on the parameters.
What I would like to acheive now is these input parameters are based on a distinct result from a query. So
SELECT DISTINCT x, y from tblTEST.
How am I able to get this resultset and loop through them to then pass it to the parameters to create the files.
At the moment, the inputs are used in the ActiveX script to provide the variables required.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

You have a tblTest and you are going to run the rest of your DTS package using distinct combinations of x and y that occur in tblTest.

What I would do is to create a temp table on one of the sql connections.

create table #temp (x int, y int)

insert into #temp select distinct x, y from tblTest

I'm a bit rusty on this, but here is where I would start. You need a second sql command on that connection that uses output paramaters to assign values.

declare @x int, @y int

select top 1 @x = x , @y = y from #temp

delete #temp where x = @x and y = @y

select @x as x , @y as y

The pain is getting those values from the output parameters.

Next in your loop you need to test to make sure that the global variable that contains the x and y values has a value. If they do, then continue in the loop otherwise you exit.

I hope this gets you started. If you need more help, I will point you to some information that will help you.

Russ

Russel Loski, MCSE Business Intelligence, Data Platform

Ged Aitchison Valued Member Points: 54 More actions · Answer 2

Sounds fine. I was looing at a possibility of using an 'Execute SQL Task' t oget the distinct results and then based on those results using something like the following.

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

dim RS

'Create the Record set

set RS = CreateObject("DistinctRecords.Recordset")

'set RS = DTSGlobalVariables("DistinctRecords").value

DTSGlobalVariables("a").value = RS.Fields("BUKRS")

DTSGlobalVariables("b").Value = RS.Fields("MONAT")

sFilename = "J:\Export\PAM\ttGLBalances\txGLBalances_WA1_BS_Annual_2007_Header_LSMWSel_"&a&"_"&b&".txt"

dtsglobalvariables("exportFilename").value = sFilename

'Go to next row

RS.MoveNext

Main = DTSTaskExecResult_Success

End Function

Russel Loski SSCertifiable Points: 7059 More actions · Answer 3

I don't recognize the statement:

set RS = CreateObject("DistinctRecords.Recordset")

Do you mean ?

set RS = CreateObject("ADO.Recordset")

You would need to open a recordset with the proper connection and command string. I haven't worked with ADO in a while so I'm not sure of the syntax.

Russel Loski, MCSE Business Intelligence, Data Platform