Solved

DTS parameter

Posted on 2004-09-09
14
3,028 Views
Last Modified: 2013-11-30
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
Comment
Question by:mcrmg
  • 6
  • 5
  • 3
14 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12018661
Post your ActiveX script....
0
 

Author Comment

by:mcrmg
ID: 12018795
'**********************************************************************
'  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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12019301
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12019308
80 points for this .. LOL .. i shoulda looked at the points first ..
0
 
LVL 34

Expert Comment

by:arbert
ID: 12019344
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
 

Author Comment

by:mcrmg
ID: 12019432
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
 
LVL 10

Accepted Solution

by:
Jay Toops earned 110 total points
ID: 12020237
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mcrmg
ID: 12020651
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
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 12020699
Yes, lookups in DTS are very slow....
0
 

Author Comment

by:mcrmg
ID: 12020727
Is there a workaround? thx
0
 
LVL 34

Expert Comment

by:arbert
ID: 12021167
Only if you can code the "join" in the sql itself or code your load job using ADO instead....
0
 

Author Comment

by:mcrmg
ID: 12021187
Is that possible that you could provide small exs?  Thx
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 12021213
Not part of your original question, but here is a good reference:  http://www.able-consulting.com/ADO_Faq.htm
0
 

Author Comment

by:mcrmg
ID: 12021240
thx...I think I will close this q for now...thx
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now