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

DJMoonLightAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.