Solved

DTS parameter

Posted on 2004-09-09
14
3,029 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
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.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server insert 13 30
Need some help to cast ntext to nvarchar SQL 2000 7 33
Return 0 on SQL count 24 30
How to use three values with DATEDIFF 3 24
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

772 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