DTS parameter

Hi,

In DTS, I have an ActiveX that create a global variable and I am sure that variable has value. When it is
successful, it then goes next step which is a Transform Data Task between two SQL connections.

In the source tab, I use that global variable as parameter, but that variable can not be passed.  I receive:

1. Run from Preview inside task:
No value given for one or more required parameters

2. Run entire DTS:
Invalid procedure call or argument: 'DTSSource'

Any thoughts?  Thx
mcrmgAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jay ToopsConnect With a Mentor Commented:
set pkg =DTSGlobalVariables.Parent
set bitsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1")
set bicus = bitsk.CustomTask
bicus.SourceSQLStatement ="SELECT     Comments_Source.Number AS SourceNum, " & _
"Comments_Source.Comments, Comments_Source.CommentsDate, " & _
"                     Static.ID, Static.Number AS DBNum " & _
"FROM         Static INNER JOIN " & _
"                      Comments_Source ON Static.Number = Comments_Source.Number " & _
"WHERE     (Static.ID = '" & rsGlobals.fields("DLNAME") & "' "


Jay
ps. DTSTask_DTSDataPumpTask_1 should be the first created data pump task.
if its not this could be DTSTask_DTSDataPumpTask_2 or 3 etc.
you can see which one it is thru "disconnected edit"

0
 
arbertCommented:
Post your ActiveX script....
0
 
mcrmgAuthor Commented:
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()


      dim conn
      dim iRowCount
      dim sName
      dim sID
      dim Flds

      set conn = CreateObject("ADODB.Connection")
      set myRecordset = CreateObject("ADODB.Recordset")



      conn.Open = "Provider=SQLOLEDB.1;Data Source=myServer; Initial Catalog=myDB;user id = sa;password=myPassword"
      
      On Error Resume Next
            returntext = Trim(InputBox("Please enter ID"))
      if(NOT isNumeric(returntext))then
            msgbox ("Please enter an integer.")
            Main = DTSTaskExecResult_Failure
      else

            SQLText = "SELECT 'rowcount' = COUNT (*) , 'ID' = ID, 'Name' = Name FROM Table1 WHERE ID=" & cint(returntext) & " GROUP BY ID, Name"
            myRecordset.Open SQLText, Conn

            set Flds = myRecordset.Fields
            set iRowCount = Flds("rowcount")
            set sID =  Flds("ID")
            set sName =  Flds("Name")

            If iRowCount.Value = 0 then
                  msgbox("Name does not exist.")      
                  Main = DTSTaskExecResult_Failure
            Else
                   OK=MsgBox( "Is this correct value:" + sName,  vbYesNo)
                  If OK=vbYes Then
                        DTSGlobalVariables("gvID").Value=sID

                        Main = DTSTaskExecResult_Success
                  else
                        Main =DTSTaskExecResult_Failure
                  end if
            End If

      end if      








       
      











End Function
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jay ToopsCommented:
You need to dynamicall configure the transform data task from
the active x script

like so

set pkg =DTSGlobalVariables.Parent
set bitsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1")
set bicus = bitsk.CustomTask
bicus.SourceSQLStatement ="select pt_no, mrn, cdate(dsch_date) as dsch_date from " & rsGlobals.fields("DLNAME")
set bitsk = pkg.Connections("2004Daily")
bitsk.DataSource = rsGlobals.fields("DLDirectory") & "\"  & rsGlobals.fields("DLPREFIX") &  rsGlobals.fields("DLSEQNUM") &  rsGlobals.fields("DLSuffix")

This code dynamically configures the data source directory for the 2004daily task
AND
Sets a sql statement for the source of the transformation task


Jay
0
 
Jay ToopsCommented:
80 points for this .. LOL .. i shoulda looked at the points first ..
0
 
arbertCommented:
Is that the right DTS script--I don't see where you're referencing an input column any where (Invalid procedure call or argument: 'DTSSource')
0
 
mcrmgAuthor Commented:
Please let me know if this is what you are looking for.  This is the query from the source tab.  Thx

SELECT     Comments_Source.Number AS SourceNum,
Comments_Source.Comments, Comments_Source.CommentsDate,
                     Static.ID, Static.Number AS DBNum
FROM         Static INNER
JOIN
                      Comments_Source ON Static.Number
= Comments_Source.Number
WHERE     (Static.ID = ?)

IN Parameter tab, I pass gvID as parameter 1
0
 
mcrmgAuthor Commented:
okay, I think I got to work now.

In the Transformation Properties, I have code as following: Just do the look up and insert into one table.  My question is, it inserts 1000 rows per 10 secs, 100 per 1 sec.  It seems a liitle slow..........
Function Main()


      DTSDestination("ID") = DTSLookups("lkStaticID").Execute(DTSSource("DBNum"))
      DTSDestination("Comments") = DTSSource("Comments")
      DTSDestination("CommentsDate") = DTSSource("CommentsDate")


      Main = DTSTransformStat_OK
End Function
0
 
arbertConnect With a Mentor Commented:
Yes, lookups in DTS are very slow....
0
 
mcrmgAuthor Commented:
Is there a workaround? thx
0
 
arbertCommented:
Only if you can code the "join" in the sql itself or code your load job using ADO instead....
0
 
mcrmgAuthor Commented:
Is that possible that you could provide small exs?  Thx
0
 
arbertConnect With a Mentor Commented:
Not part of your original question, but here is a good reference:  http://www.able-consulting.com/ADO_Faq.htm
0
 
mcrmgAuthor Commented:
thx...I think I will close this q for now...thx
0
All Courses

From novice to tech pro — start learning today.