sql syntax for Data Driven Query

Posted on 2004-11-22
Last Modified: 2013-11-30

I am quite new to DTS but have decided (so far anyway) that a Data Driven Query is the solution to my problem of keeping a SQL Server database updated from an old Sybase Adaptive Server anywhere db.

We originally used the Transform data task which is great and fast but we need to ability to update the record if it exists in the destination the next time the transfer is done.

We have placed a Data Driven Query in our visual editor within EM and have set (what we think) it up correctly but when we try and run it we receive the following error :-

Error Source : Microsoft Data Transformation Services (DTS) Data Pump
Error Description : DataDrivenQuery: No query specification returned by Transform Status.

Here is the basic (for our test) query for INSERT :-

INTO TicketHeaders(TicketNumber, Contract, Currency, Exchange)
VALUES     (?, ?,?,?)

Any thoughts to what we are doing wrong?


Question by:JAMES
    LVL 13

    Expert Comment

    Could you give some more information please -->
     ie the paramater place holders you have in the insert statement
    have they been assigned to global variables, where do you set these global variables?

    Author Comment

    We dont set them - we assumed they would be populated from the "source" table as they are mapped in the transformations tab.
    LVL 13

    Accepted Solution

    nope thats your problem, the parameter place holders are for inputs into a query and must be set explicitly
    You willl notice the parameter tab on the DataDriveQuery task , when you click this then you will notice a list in which  you assign global variables to the parameters.

    What you rather want to do , is  program the logic into the ActiveXScript of the Transform Task (DTSDestination...  = DTSSource... ),
    You could make use of the Lookups here .. Lookup will return a single value for each processed row from which you could for example
    determine if the record already exists.

    You would check out this site, it is an excellent resource for DTS programming


    Author Comment

    Great thanks.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now