Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

dts package error

Hello,

What does this mean? I have a DTSGlobalvariables called counter_name.

Why would I ge this error?

The number of failing rows exceeds the maximum specified. Query cannot be updated because it contains no searchable

columns to use as a key.

thanks,

Don
0
dprice7
Asked:
dprice7
  • 5
  • 3
1 Solution
 
Anthony PerkinsCommented:
This is in continuation to this thread:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21396595.html

Perhaps we can see the query in question?
0
 
dprice7Author Commented:
This process has two scripts that are made from separate tabs. The first script is from the first tab that uses build query.

SELECT     vw_T_Head.WeekEnding, vw_T_Head.Clock_Num, vw_T_Head.dept_id,
vw_T_Detail.T1, vw_T_Detail.T2, vw_T_Detail.T3, vw_T_Detail.T4,

                      vw_T_Detail.T5, vw_T_Detail.T6, vw_T_Detail.T7,
vw_T_Detail.P_Num
FROM         vw_T_Head  INNER
Join
                      vw_T_Detail
ON vw_T_Head.T_Num=vw_T_Detail.T_Num
WHERE     (vw_T_Head.WeekEnding
= '03/12/2005')



The second is from the transformations tab properties acive x script function.
It tests fine but when executed from the main task it does not work..


*********************************************************************
'  Visual Basic Transformation Script to move data from sql server to iseries
'************************************************************************

'  Copy each source column to the destination column

Function Main()

Dim deptsub
Dim deptId
Dim datechar
Dim datePassed,  dd,  mm,  yyyy
i = 0
' Verify it belongs to Engineering dept first

deptId = Mid(DTSSource("dept_id"), 2, 3)
 
  If deptId = "225"  Or   deptId = "228" Then



DTSDestination("LBTIM") = cdbl(DTSSource("T7")) + cdbl(DTSSource("T6")) +  cdbl(DTSSource("T5")) + cdbl(DTSSource("T4")) + cdbl(DTSSource("T3")) +  cdbl(DTSSource("T2")) _
             + cdbl(DTSSource("T1"))

 datechar=Split(DTSSource("WeekEnding"), "/")

' datePassed=datechar(0) + datechar(1) + datechar(2)

mm = datechar(0)
dd = datechar(1)
yyyy = datechar(2)
 yyyy=1 & Right(yyyy,2)



if len(mm) =1 then
    mm = "0"+mm
end if

if len(dd) =1 then
    dd = "0"+dd
end if



datePassed=yyyy + mm + dd

DTSDestination("TRNDT")= datePassed
'  DTSDestination("TRNDT")=1 & _
'  Right(DTSSource("WeekEnding"),2) & Left(DTSSource("WeekEnding"),2) + Mid(DTSSource("WeekEnding"),3,2)

'  DTSDestination("TRNDT")=1 & _
'  Right(DTSSource("datechar"),2) & Left(DTSSource("datechar"),2) + Mid(DTSSource("datechar"),3,2)
       

' DTSDestination("TRNDT") = CDATE(DTSSource("WeekEnding"))
DTSDestination("EMPNO") = DTSSource("Clock_Num")

DTSDestination("QTSCP") = 0
DTSDestination("QTCOM") = 0
DTSDestination("TERML") = "PECOIISQL"
DTSDestination("RCDCD")= "PA"
DTSGlobalVariables("counter_name").Value = DTSGlobalVariables("counter_name").Value + 1
DTSDestination("SEQNM") = DTSGlobalVariables("counter_name").Value

'Determine work center and if a special Mo

If DTSSource("dept_id") = "225" Then
DTSDestination("wkctr") = "22500"
End If

If DTSSource("dept_id") = "228" Then
DTSDestination("wkctr") = "22800"
End If

deptsub = Mid(DTSSource("P_Num"), 1, 3)  
deptId = Mid(DTSSource("dept_id"), 2, 3)  

DTSDestination("DPTNO") = deptId
If    deptsub = "M70" Then

   If deptId = "225"  Then
      DTSDestination("wkctr") = "22501"
      DTSDestination("OPSEQ") = "0012"
      DTSDestination("ORDNO") = DTSSource("P_Num")
   End If
 
   If deptId = "228"  Then
      DTSDestination("wkctr") = "22801"
       DTSDestination("OPSEQ") = "0130"
      DTSDestination("ORDNO") = DTSSource("P_Num")
   End If
 
ELSE

'Determine work center when not a special Mo and use defaults for Ordno


   If deptId = "225"  Then
     DTSDestination("ORDNO") = "M072250"
     DTSDestination("wkctr") = "22500"
     DTSDestination("OPSEQ") = DTSSource("P_Num")
   End If

    If deptId = "228"  Then
     DTSDestination("ORDNO") = "M072280"
     DTSDestination("wkctr") = "22800"
     DTSDestination("OPSEQ") = DTSSource("P_Num")
   End If


End If


End If
      Main = DTSTransformStat_OK
End Function
0
 
Anthony PerkinsCommented:
Change this:
SELECT     vw_T_Head.WeekEnding, vw_T_Head.Clock_Num, vw_T_Head.dept_id,
vw_T_Detail.T1, vw_T_Detail.T2, vw_T_Detail.T3, vw_T_Detail.T4,

                      vw_T_Detail.T5, vw_T_Detail.T6, vw_T_Detail.T7,
vw_T_Detail.P_Num
FROM         vw_T_Head  INNER
Join
                      vw_T_Detail
ON vw_T_Head.T_Num=vw_T_Detail.T_Num
WHERE     (vw_T_Head.WeekEnding
= '03/12/2005')

To this:
SELECT      vw_T_Head.WeekEnding,
         CONVERT(varchar(30), vw_T_Head.WeekEnding, 112) WeekEnding2,
                  vw_T_Head.Clock_Num,
                  vw_T_Head.dept_id,
                  vw_T_Detail.T1,
                  vw_T_Detail.T2,
                  vw_T_Detail.T3,
                  vw_T_Detail.T4,
                  vw_T_Detail.T5,
                  vw_T_Detail.T6,
                  vw_T_Detail.T7,
                  vw_T_Detail.P_Num
FROM            vw_T_Head  
                  INNER Join vw_T_Detail ON vw_T_Head.T_Num=vw_T_Detail.T_Num
WHERE            vw_T_Head.WeekEnding = '2005-03-12'

_________________________________________________________________________________
Change this:
 datechar=Split(DTSSource("WeekEnding"), "/")

' datePassed=datechar(0) + datechar(1) + datechar(2)

mm = datechar(0)
dd = datechar(1)
yyyy = datechar(2)
 yyyy=1 & Right(yyyy,2)



if len(mm) =1 then
    mm = "0"+mm
end if

if len(dd) =1 then
    dd = "0"+dd
end if



datePassed=yyyy + mm + dd

To:
datePassed = DTSSource("WeekEnding2")
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Anthony PerkinsCommented:
And add MsgBox code everywhere to troubleshoot.
0
 
dprice7Author Commented:
acperkins,

Could you give me a short example of how the MsgBox code looks and works?

thanks,

Don
0
 
Anthony PerkinsCommented:
It is as simple as double clicking on the MsgBox function in the list on the left and it display the template

MsgBox( <<prompt>> , <<buttons>> , <<title>> , <<helpfile>> , <<context>> )

an example could be:

MsgBox(datePassed)
0
 
dprice7Author Commented:



DTSGlobalVariables("counter_name").Value = DTSGlobalVariables("counter_name").Value + 1
DTSDestination("SEQNM") = DTSGlobalVariables("counter_name").Value



Do I have to declare this DTSGlobalVariable (counter_name) on the first parameters tab in order to use it like this or will it automatically add it as I write the code?




To this:
SELECT     vw_T_Head.WeekEnding,
         CONVERT(varchar(30), vw_T_Head.WeekEnding, 112) WeekEnding2,
               vw_T_Head.Clock_Num,
               vw_T_Head.dept_id,
               vw_T_Detail.T1,
               vw_T_Detail.T2,
               vw_T_Detail.T3,
               vw_T_Detail.T4,
               vw_T_Detail.T5,
               vw_T_Detail.T6,
               vw_T_Detail.T7,
               vw_T_Detail.P_Num
FROM          vw_T_Head  
               INNER Join vw_T_Detail ON vw_T_Head.T_Num=vw_T_Detail.T_Num
WHERE          vw_T_Head.WeekEnding = '2005-03-12'


Can I change the date to be a passed in parm or global variable too?
Does that mean I will end up with two GlobalVariables when I am done?

thanks,

Don
 
 
0
 
Anthony PerkinsCommented:
You declare global variables in the "Global Variables" tab in the Package Properties.

>>Can I change the date to be a passed in parm or global variable too?<<
Yes, you can use global variables for this, as in:

SELECT     vw_T_Head.WeekEnding,
         CONVERT(varchar(30), vw_T_Head.WeekEnding, 112) WeekEnding2,
               vw_T_Head.Clock_Num,
               vw_T_Head.dept_id,
               vw_T_Detail.T1,
               vw_T_Detail.T2,
               vw_T_Detail.T3,
               vw_T_Detail.T4,
               vw_T_Detail.T5,
               vw_T_Detail.T6,
               vw_T_Detail.T7,
               vw_T_Detail.P_Num
FROM          vw_T_Head  
               INNER Join vw_T_Detail ON vw_T_Head.T_Num=vw_T_Detail.T_Num
WHERE          vw_T_Head.WeekEnding = ?

Then click on the "Properties" button to assign a global variable.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

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