Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

DTS package dynamic update

Ok I have a DTS package currently. It reads an Excel file and checks to see if the info is in the DB - if it is then it updates the info, if it is not then it updates it. Now it has been a while since I wrote these, and been a while since I did DB programming. I would like to add a new feature to the package that will read the VIN number of the unit coming in, if that unit has an F at the beginning of it then I want to add a certain fuel type to the record. So I came up with the following.... which is not working, which is why I am asking this question.

DTSDestination("StockNo") = DTSSource("STOCK-NO#")
      DTSDestination("Year") = YR
      DTSDestination("Make") = DTSSource("MAKE")
      DTSDestination("Model") = DTSSource("MODEL")
      DTSDestination("ModelNo") = DTSSource("MODEL-NO")
      DTSDestination("Mileage") =DTSSource("ODO")
      DTSDestination("Options") = DTSSource("OPTIONS")
      DTSDestination("Color") = DTSSource("COLOR")
      DTSDestination("Loc") = DTSSource("LOC")
      DTSDestination("CurrentLocation") = DTSSource("LOC")
      DTSDestination("Retail") = DTSSource("RETAIL")
      DTSDestination("Days") = DTSSource("DAYS")
      DTSDestination("SerialNo") = DTSSource("SERIAL-NO#")

      if DTSSource("BODY") = "MHA" or DTSSource("BODY") = "MHC" then --- Don't care about the other body types just these 2
            GasorD = instr(DTSSource("SERIAL-NO#"), "4")                --- should return 1 if the serial starts with a 4
            if GasorD = "1" then
                  DTSDestination("FuelType") = "Diesel"
                  DTSDestination("FuelType") = "Gas"
            end if
      end if

      DTSDestination("Body") = DTSSource("BODY")
      DTSDestination("Class") = DTSSource("BODY")
      DTSDestination("DateUploaded") = Date
      DTSDestination("OrderDate") = Date

      Main = DTSTransformStat_InsertQuery
Now in the insert query itself looks like this

INTO Inventory
                      (StockNo, [Year], Make,
Model, ModelNo, Options, Color, Loc, CurrentLocation, Retail, Days,
SerialNo, Body, NewUsed, Live, Class, DateUploaded, OrderDate, fueltype)
VALUES     (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'Used', ?, '1', ?, ?, ?,?)

What do I need to do to fix this?
  • 4
1 Solution
poulsborvAuthor Commented:
What might work better is if I changed Class to MHD (since Class and Body are the same in the DB)
What are you getting now?

Personally, I would do something like this

left(DTSSource("SERIAL-NO#"), 1)="4"

If you use INSTR, you're likely to get more matches than you should....

poulsborvAuthor Commented:
Ok that is a better way of doing that, but the fueltype still does not get inserted into the dB

 if DTSSource("BODY") = "MHA" or DTSSource("BODY") = "MHC" then
      GasorD = left(DTSSource("SERIAL-NO#"), 1)="4"
          if GasorD = "1" then
               DTSDestination("FuelType") = "Diesel"
               DTSDestination("FuelType") = "Gas"
          end if
     end if
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

check your parameters for the insert query to make sure FuelType is being passed.
also. I think the parameters are case sensitive so you might want to double check spelling and case.
poulsborvAuthor Commented:
The spelling and stuff looks good, I updated the insert and update statements

UPDATE    dbo.Inventory
SET              Mileage = ?, Options
= ?, Color = ?, CurrentLocation = ?,
Days = ?, NewUsed = 'New', Body = ?, Class = ?, Live = 1, FuelType = ?
WHERE     (StockNo = ?)

poulsborvAuthor Commented:
I figured it out, it was my update statment, my variables were not parsed correctly, thanks to both


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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