Hi All,
I need some help here!
(AS/400) Source file has a field called 'Process_date' and
the same is there in the (SQL Server) destination table.
I want to compare max (Process_date) of the destination
with the source. Only if it doesn't match, the DTS package
should run. Otherwise, it should not run at all.
Can anyone suggest a way to do this?
Thanks
Kris

Ok So what you do is this

You select the AS400 MAX(Process_Date) attribute into a GV using the
ExecuteSQL Task.
You select from the destination SQL Server with something like

SELECT COUNT(*) as Matches FROM SQL_Table WHERE Process_Date = ?

You assign the "Matches" to a Global Variable and you feed your AS400
variable in as the ?

In an active Script task you then use Workflow to say

dim stp

set stp = DTSGlobalVariables.Parent.Steps("Name of following step")

if DTSGlobalVariables("My_SQL_Val").Value > 0 THEN
stp.DisableStep = True
else
stp.DisableStep = False
end if

If you need any more help with this then shout


--
--

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


"Kris" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hi,
Thanks for the help.
I tried, it didn't work. May be I am doing something wrong
here.

This is what I was trying,

ExecuteSQL Task 1 = "src" (Source - AS400)

select MAX(curdat) as process_date from sales
<into an output global variable "SRCVAL">
ExecuteSQL Task 2 = "trg" (Target - SQL Server)

SELECT MAX(PROCESS_DATE) as Matches FROM Sales_Dtl <into
an input global variable "TRGVAL">

Created an ActiveX

dim stp

set stp = DTSGlobalVariables.Parent.Steps("Transform Data
Task: 1")

if DTSGlobalVariables("SRCVAL").Value =
DTSGlobalVariables("TRGVAL").Value THEN
stp.DisableStep = True
else
stp.DisableStep = False
end if

And in the workflow, I connected 2 tasks to the activeX
task and the to the main extraction task.

It will be nice if you can tell how shud I modify it , to
make it work.

Thanks
Kris.

OK

It didn't work does not help me figure out your problem !

You select the value from the DB2 and assign to a GV. Looks OK to me - Does
it work?
The check on the SQL Server is wrong though. You want to know if any values
exist with that value in the GV not whether the MAX is the same on SQL
Server as on DB2. You should be doing a COUNT(*) as AmountOfRows - Besides
that do you get a value in this variable?

Is your Step really called "Transform Data Task: 1". I would seriously
doubt it.

Right click on the Task and look in the workflow properties' options tab


In your Active Script task you then do

If Cint(DTSGlobalVariables("AmountOfRows").Value) >= 1 then
'There is rows matching
stp.Disablestep = True
...
..



--
--

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


"Kris" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Hi,
I created a GV called "AmountOfRows" and its the result of
COUNT (*) FROM DESTINATION TABLE
This returns entire count that includes multiple process
dates.

I created another GV called "SRCVAL" , its the result of
Select MAX(process_date) FROM SOURCE TABLE.

I executed both the tasks and checked the properties of
the package. I saw the results as the values of the
variable. They are 20040614 (String) & 400 (Int).

And I checked the name of the step, it
is "DTSStep_DTSDataPumpTask_1"

Which is used in the ActiveX...

Function Main()
dim stp

set stp = DTSGlobalVariables.Parent.Steps
("DTSStep_DTSDataPumpTask_1")


If Cint(DTSGlobalVariables("AmountOfRows").Value) >= 1 then

stp.DisableStep = True
else
stp.DisableStep = False
end if
End Function
***

When I right click and execute the ActiveX to test it.
It says "Invalid Task Result Value"

I am kind'a struck here.

Kris.

You need to set the function return value

so

Function Main

....
...
..

Main = DTSTaskExecResult_Success

End Function


--
--

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


<anonymous (AT) discussions (DOT) microsoft.com> wrote

Thankyou very much!
It works now! But only the first step runs if the process
date of target doesn't match with the source. The other
steps don't. The workflow properties have been set to
perform the current step in the even of success of the
precedence.

If the process dates are matching, everything stops, which
is good. It doesnt work the otherway. Only the first one
works.

I don't know the reason.
Do I have to define all the other steps too in the ActiveX?
I am sorry to bother you much on this.

Kris.

Hi Allan Mitchell,
It works well now! I created an ActiveX like this and used
it in a different package. It worked.
Thanks a lot for your help.
Have a great day
Kris.