OK Let's take a very simple example

I have a table in SQL Server that will hold my settings

CREATE TABLE GVPopVals
(
FileName varchar(200)
)

Ths will hold a set of filenames that i want to export to. I will be
exporting Authors.

INSERT GVPopVals VALUES('d:\MyFirstFile.txt')
INSERT GVPopVals VALUES('d:\MySecondFile.txt')


I want to populate each file with records from authors and I'm going to do
this by looping through a recordset.

Task 1 --Setup the recordset - ExecuteSQL task populates a Rowset GV (or use
an ADO connection)
Task 2 -- Active Script task. - Load the recordset into an array and set up
Global Variables
Task 3 -- Loop through the array keeping count of where we are by using a
GV. Populate the DtaSource property of the text file.
Task 4 -- DataPump (Betwen Authors and some text file which won't be used of
course)
Task 5 -- Loop around


Task 2

Function Main()

dim pkg
dim rs
dim BigString
dim arr
dim cn



set pkg = DTSGlobalVariables.Parent

set cn = CREATEOBJECT("ADODB.Connection")
set rs = CREATEOBJECT("ADODB.Recordset")
'set rs = DTSGlobalVariables("rValue").Value
cn.ConnectionString="Provider=SQLOLEDB;Data Source=.;Initial
Catalog=Pubs;Integrated Security=SSPI"
cn.Open

set rs = cn.Execute("SELECT filename from GVPopVals")

arr = rs.GetRows


DTSGlobalVariables("arrValues").Value = arr
DTSGlobalVariables("Counter").Value = 0
DTSGlobalVariables("MaxValueOfArray").Value = UBOUND(arr) + 1

rs.Close

Main = DTSTaskExecResult_Success
End Function

Task 3

Function Main()

dim stpPump
dim tskPump
dim pkg



set pkg = DTSGlobalVariables.Parent
set tFile = pkg.Connections("Dest")

set stpPump = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set tskPump = pkg.Tasks("DTSTask_DTSDataPumpTask_1")


If ShouldIloop = True then
tFile.DataSource = DTSGlobalVariables("fName").Value
stpPump.DisableStep = False
Else
stpPump.DisableStep = True
End if

Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop

dim arr
arr = DTSGlobalVariables("arrValues").Value

if DTSGlobalVariables("Counter").Value <=
Cint(DTSGlobalVariables("MaxValueOfArray").Value) THEN

DTSGlobalVariables("fName").Value =
arr(0,DTSGlobalVariables("Counter").Value)
DTSGlobalVariables("Counter").Value =
Cint(DTSGlobalVariables("Counter").Value) + 1
ShouldILoop = CBool(True)
Else
ShouldILoop = CBool(False)
End if
End Function


Task 5

Function Main()

dim pkg
dim stp

SET pkg = DTSGlobalVariables.Parent
SET stp = pkg.Steps("DTSStep_DTSActiveScriptTask_1")

stp.executionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success
End Function


Whilst this may not be exactly what you are looking for it should give you a
starting point.





--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"roxindigo" <member37650 (AT) dbforums (DOT) com> wrote

I'm overwhelmed by the level of assistance you have provided. It never
occurred to me to use an array. I will give this a try. Thanks!!!



Karen


--
Posted via http://dbforums.com

In article <3324249.1062598318 (AT) dbforums (DOT) com>, roxindigo
<member37650 (AT) dbforums (DOT) com> writes

I think an array is making hard work of the job, especially as an array
can be a pain itself due to the strange type it uses and the fact that
the package cannot be saved in this state.

You can use the recordset object directly to drive a loop, and to save
messing around with ADO I would use the Execute SQL Task to get the RS
object into a global variable.

The script confused me, since you appeared to do
objresults.MoveNext
before reading any values, which means you will
always miss the first row. You also set a step to waiting, but then
continued with the loop, so it wouldn't get a chance to execute. I may
have misread this however.

If you want a nice sample of driving a loop of tasks from a SQL
recordset query then let me know and I'll mail it direct. I never got
round to writing it up for the site, but I will.

If however you are happy with your arrays then just ignore this and
stick with what works for now.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

As Darren has said his example is a dream, he never actually said that I
did, and works a dream. I would also mail Darren and ask to look at his
package and than you may choose to go with his idea and use mine for
something else.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote in
message news:q6u+HhUbSlV$Ew7u (AT) sqldts (DOT) com...

Here is the article-

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Hello,
I read and looked at Darren's article "How to loop through a global
variable Rowset". I have a little different need. I need to get a
subset of data from a production server to a development server. The
data is relative based on a colum 'CFcKey'. I gathered 20,000 records
out of the primary table and want to use the pkey to gather the related
(n) records from a secondary table on the production server then send
the result into the sister table on the development server. I can think
of sending the result sets to files then uploading the file into the
development server. Can anybody think of a way to skip the 'sending
results to a file' step and get the data from Prod to Dev?
Any assistance is greatly appreciated, I am bout whipped!
Thanks
Bill


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

why dont you create a linked server to the data you need, and perform a SELECT INTO from the linked server to the server you want to insert into. You have to run the query on the server where you are inserting the data, and you have to have execute permissions on the server you are querying the data from. HTH

Cheers,

Casualinfoguy

----- LilBill wrote: -----


Hello,
I read and looked at Darren's article "How to loop through a global
variable Rowset". I have a little different need. I need to get a
subset of data from a production server to a development server. The
data is relative based on a colum 'CFcKey'. I gathered 20,000 records
out of the primary table and want to use the pkey to gather the related
(n) records from a secondary table on the production server then send
the result into the sister table on the development server. I can think
of sending the result sets to files then uploading the file into the
development server. Can anybody think of a way to skip the 'sending
results to a file' step and get the data from Prod to Dev?
Any assistance is greatly appreciated, I am bout whipped!
Thanks
Bill


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Are both tables on the Production Server? It sounds from your description
like they are. Can you not issue a standard Query ? If I have
misunderstood could you explain more (How many servers are involved etc)



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"LilBill" <LBill (AT) NoSpam (DOT) com> wrote

There are two servers, A (Production) B (Development).
Server A's primary table has well over 5 million records, with multiple
related records in each of the other 9 tables. I want to get about
20,000 of the primary tables records, then only the related records from
the other 9 tables from server A to B. I was attempting to use a DTS
package to faciliate the data moving processes.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Then if all the 9 tables are in one place (server) then create a view of the
data. You then use the view(s) as the SourceObjectName for each of your
datapump(s).

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"LilBill" <LBill (AT) NoSpam (DOT) com> wrote