DTS package dynamic update

Posted on 2005-02-27
Medium Priority
Last Modified: 2013-11-30
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?
Question by:poulsborv
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4

Author Comment

ID: 13416552
What might work better is if I changed Class to MHD (since Class and Body are the same in the DB)
LVL 34

Expert Comment

ID: 13416905
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....


Author Comment

ID: 13421314
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

SoftEng007 earned 1500 total points
ID: 13422829
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.

Author Comment

ID: 13423868
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 = ?)


Author Comment

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


Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question