Hi all,
could anybody please be so kind and help me with the following:

I just tried to create a sample for checking some options. So I have the
following construct:

EXECUTE SQL Task (1):
SELECT ID FROM Users WHERE gender = 'female'

this rowset is stored in the following global variable:
Name: rsDta
Type: other

As far as I understand this is a recordset.


Now I want to transfer data (First - / Last-Name) to another table.
The idea is to do that between other servers later on.

Now I create a "Transform Data Task" to move the data based on the global
Variable.
Here starts the trouble.
How can I use the global rowset as reference for the transform Data Task.

I tried the following in the SQL:

SELECT FirstName, LastName FROM AllUserBase WHERE ID IN ? (? mapped to
rsDta)


I always get Access Violations. Do I need to do something else to archive
that?
Or is the way to use a global rowset in another Transform Data Task wrong?

Please be so kind and point me into the right direction,

Thanks in advance

Andreas Bretl
andreas.bretl (AT) brainlab (DOT) com

Are you not complicating things?

Would this work instead

SELECT FirstName, LastName
FROM AllUserBase
WHERE ID IN
(
SELECT ID FROM Users WHERE gender = 'female'
)


You do not mention if the statement in the parentheses is on a remote SQL
Server. if it is then you could use a linked server. Your current
construct will not work.

You can maniuplate a Rowset like this

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


--

----------------------------

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


"Andreas.Bretl" <bretl (AT) brainlab (DOT) com> wrote

Hi Allen,
it might be that I am complicating things but the problem is that the rowset
is fetched from my SQL Server
and the corresponding data is fetched from IBM DB2 (using ODBC).

So subqueries unfortunately won't work.

I haven't mentioned that above, so sorry for that.

But I'll have a look into the article you attached.

Thaks a lot Allen for your comment,

Seize the Day

Andreas Bretl
andreas.bretl (AT) brainlab (DOT) com

Then you can either use a linked server OR you transfer the data from

SELECT ID FROM Users WHERE gender = 'female'

to DB2 and then do the Query from the DB2 side.

Should be quicker than using the Rowset approach

--

----------------------------

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


"Andreas.Bretl" <bretl (AT) brainlab (DOT) com> wrote

Here starts the problem
I have no administrative permissions for the DB2 (it is not very well
implemented - not my fault)
and can't therefore call that from the DB2.
Due to the fact that I need all data on the SQL Server side I thought about
importing the whole tablestructure from DB2 but that would be too many rows
(2 Tables). Also I would need to import them on a scheduled base (fast
changing data).

So I am searching for a SQL Side approach.

Currently I created an ActiveX Script that builds the complete query (SELECT
* from y WHERE z IN ('constructed string')) and passes it to the Data Pump
Task.
That works but it seems a bit "strange". I read about manipulatin the Data
Pump (http://www.sqldts.com/article.asp?nav=1,6,205,7,1
) and thought "Hey, this sounds interesting". But after assigning the
Satement I need to execute the DataPump Task somehow:

Here is some code how I want to deal with it (if applicable):

Function Main()
Dim oPkg, oDataPump, sSQL, vDta

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

DIM vDta, x
vDta = DTSGlobalVariables("rsDta").Value.GetRows

FOR x = 0 to UBOUND(vDta,2)

sSQL = "SELECT SHDOCO FROM PRODDTA.F42001 WHERE SHDOCO = " & vDta(0,x) &
""
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQL

NEXT

SET oPkg = Nothing
SET oDataPump = Nothing

Main = DTSTaskExecResult_Success

End Function

Here I assign the constructed SQL Statement to the SourceSQLStatement and
now I only need to fire the Data Pump within the loop.
I have no idea how to do that

Thanks again Allan for your ideas,

Andreas Bretl
andreas.bretl (AT) brainlab (DOT) com

OK

So what you do is you loop over the Rowset. You grab each value and
concatenate it into a string. You then insert this whole concatenated string
into your SELECT string. You assign this string to the SourceSQLStatement
of the DataPump task.


--

----------------------------

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


"Andreas.Bretl" <bretl (AT) brainlab (DOT) com> wrote

Yes, that's right.
But how do I execute the Data Pump Task in every loop after assigning the
SourceSQLStatement?

I also tried the following:
- Creating a comma-seperated string building the IN Clause
- Assigning that string to a global variable
- using parameters in the SQL Statement of the DataPump:
SELECT xxx FROM yyy WHERE xxx IN ?

But it also doesn't work..

Is it generally possible to assign a complete rowset using parameters?
Like the sample above but using the global variable holding the rowset?
Maybe like this:
SELECT xxx FROM yyy WHERE xxx IN (SELECT xxx FROM ?)



"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Why do you need to do a loop?

You concatenate all the values in the rowset together and fire the DataPump
once.

so you concatenated rowset string would look like

1,3,2,8,9................

You then add it to your

SELECT ............. FROM TABLE WHERE Column in(

and fniish it up with the ) for the end of the statement.



--

----------------------------

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


"Andreas.Bretl" <bretl (AT) brainlab (DOT) com> wrote

That's how I do it right now (like you mentioned before). I concatenate the
ID-Values in the loop (ActiveX) and send the generated query to the
SourceSQLStatement (see below). Thats the only way it works but what I
mentioned before, it looks like a workaround (I do not want to blame
anybody).
I just wondered if it would be possible to solve the problem more elegant
(if it is an unelegant way - I have no clue).
It would also be nice to get a deeper look into the whole topic (I read SQL
Books Online and several other articles that often only generate single
queries in the loop and parse the query to the SourceSQLStatement. But I
never found out how to fire the DataPump within an ActiceX-Script Loop. It
looked like parsing every generated query [I could see that] but never
telling the chaged DataPump to execute with the new query)

I appologize if my english is too bad for explaining my problem as I'd like
to do

Thanks again Allan for your efforts, I really appreciate that

Andreas Bretl
andreas.bretl (AT) brainlab (DOT) com


Function Main()

DIM vDta, x
Dim sInClause

vDta = DTSGlobalVariables("rsDta").Value.GetRows

sInClause = ""

FOR x = 0 to UBOUND(vDta,2)

'Building Query
sInClause = sInClause & vDta(0,x)
IF NOT x = UBOUND(vDta,2) THEN sInClause = sInClause & ","

NEXT

Dim oPkg, oDataPump, sSQL

' Build new SQL Statement
sSQL = "SELECT SHDOCO FROM PRODDTA.F4201 WHERE SHDOCO IN (" & sInClause &
")"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQL

SET oPkg = Nothing
SET oDataPump = Nothing


Main = DTSTaskExecResult_Success
End Function

Workaround or not that is one way of doing it. Another would be to use a
Linked Server to the DB2 database from the SQL Server database and use
something like OPENQUERY()

This is the less convoluted way of doing it.

--
--

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


"Andreas.Bretl" <bretl (AT) brainlab (DOT) com> wrote