Solved

DTS parameter

Posted on 2004-09-09
14
3,032 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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
 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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