• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3045
  • Last Modified:

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
0
mcrmg
Asked:
mcrmg
  • 6
  • 5
  • 3
3 Solutions
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
Jay ToopsCommented:
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
 
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
 
arbertCommented:
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
 
arbertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now