We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

DTS - Import Text file and update records

DJMoonLight
DJMoonLight asked
on
Medium Priority
622 Views
Last Modified: 2013-11-30
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

Comment
Watch Question

Author

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?

Author

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.

Author

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

Author

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

Commented:
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

Author

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
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2012

Commented:
Are you using a "Data Driven Query Task" or a "Transform Data Task"?

Author

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.



Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.