[Webinar] Streamline your web hosting managementRegister Today

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

DTS - Import Text file and update records

I am attempting to create a DTS package that will import a text file and update the records.

The text file has two columns:
col001 -> numID
col002 -> y/n

The db table has:
numID
Accepted
Declined

So, col002 if it is y it will update accept  is no - it will update declined.  

Here is my code:

Function Main()
      Select Case ("PolicyNumber")
            Case "Update"
                  If ("Col003" = "Y" )then
                        DTSDestination("Accepted").Value = DTSSource("Col3").Value
                  Else
                        DTSDestination("Declined").Value = DTSSource("Col3").Value
                  end if

      Main = DTSTransformstat_UpdateQuery

      End Select
End Function

Now, when i got into Queries, i don't have any parameters.  How do i get them to show up or set them up?

Thanks

0
DJMoonLight
Asked:
DJMoonLight
  • 6
  • 2
1 Solution
 
DJMoonLightAuthor Commented:
Well, i figure out the first question.  I had my source and destination reversed..

Now, since the text file is returning T/F - the field i am attempting is a bit field - so it wants a 1/0.  How do i do this?
0
 
DJMoonLightAuthor Commented:
Well, i changed my code to:

Function Main()
      Select case("Col001")
            Case "Update"
                  If ("Col003" = "Y") Then
                        DTSDestination("Approved") =  1
                  else
                        DTSDestination("Declined") = 0
                  end if
      
      Main = DTSTransformstat_UpdateQuery
      End Select
End Function

But i am still getting an error:

Active Script Transform Server "DTSTransformation_1" scrip function main retunred an invalid DTSTransaformStatus Value, or an ActiveX Script did not return an integral type.  No further work will be done by the DataPump.
0
 
DJMoonLightAuthor Commented:
Found yet another problem:

New Code

Function Main()
      Select case trim(DTSSource("Col001"))
            Case "Update"
                  If ("Col003" = "Y") Then
                        DTSDestination("Approved") =  1
                        DTSDestination("Declined") = 0
                  else
                        DTSDestination("Approved") = 0
                        DTSDestination("Declined") = 1
                  end if

            DTSDestination("PolicyNumber") = DTSSource("Col001")
      
      Main = DTSTransformstat_UpdateQuery
      End Select
End Function
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
DJMoonLightAuthor Commented:
I think this is as far as i can go troubleshooting..  A few more fixes to the code and i am still getting the able error.

Function Main()
      Select case trim(DTSSource("Col001"))
            Case "Update"
                  If (DTSSource("Col003") = "Y") then
                        DTSDestination("Approved") =  "1"
                        DTSDestination("Declined") = "0"
                  else if (DTSSource("Col003") = "N") then
                        DTSDestination("Approved") = "0"
                        DTSDestination("Declined") = "1"
                  else
                                    
                        DTSDestination("Approved") = "0"
                        DTSDestination("Declined") = "0"
                  end if
                  end if

            DTSDestination("PolicyNumber") = DTSSource("Col001")
      
      Main = DTSTransformStat_UserQuery
      End Select
End Fun
0
 
WiGgLrCommented:
Is this right? DTSSource("Col003")  or should you be using DTSSource("Col002")?


You could simplify the whole lot by loading the file as is into a temporary table then performing an update statement to transfer the data into your live table:

For example:
DTS Step 1 -> execute sql -> truncate table tblIncomingRecords  (ensures temporary table is empty)

DTS Step 2 -> transform the file into tblIncomingRecords (no extra VBScript required in this.. dump the 2 columns straight into text fields)

DTS Step 3 -> execute sql ->
update D
set D.Approved = case when T.Col003 = 'Y' then 1 else 0 end, D.Declined = case when T.Col003 = 'N' then 1 else 0 end
from tblIncomingRecored T inner join tblLiveData D on T.numID = D.numID
0
 
DJMoonLightAuthor Commented:
yes - it should be DTSSource("Col001")..

I would love to load into a temp table - but can't :( -- must do it this way...  errrr..

Here is my latest code:

Function Main()
      Select case trim(DTSSource("Col001") )
      
      Case "Update"

      DTSDestination("PolicyNumber") = DTSSource("Col001")

      If (DTSSource("Col003") = "Y") then
            DTSDestination("Approved") = "1"
            DTSDestination("Declined") = "0"
      else if (DTSSource("Col003") = "N") then
            DTSDestination("Approved") ="0"
            DTSDestination("Declined") = "1"
      else                                    
            DTSDestination("Approved") = "0"
            DTSDestination("Declined") = "0"
      end if
      end if            
      
      Main = DTSTransformStat_UpdateQuery
      End Select
End Function


Still getting the error from above
0
 
WiGgLrCommented:
Well, I haven't done transformations in the way you are at the moment, I always load into a table first :)

Here are a few things I see in your code though:

-You are checking the value of DTSSource("Col001")  in your select case statement, then only running the rest of the code if the value is "Update". If the value is "Update", you are then assigning that value ("Update") to the PolicyNumber field.

-You are evaluating DTSSource("Col003"), but if I read you correctly still, you only have 2 columns in your incoming file
0
 
Anthony PerkinsCommented:
Are you using a "Data Driven Query Task" or a "Transform Data Task"?
0
 
DJMoonLightAuthor Commented:
I am using a Data Driven Query Task..

As for me not evaluting the other two fields in the text file, i don't need to process them.



0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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