Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 20th, 2006, 04:14 PM
Authorized User
 
Join Date: Oct 2003
Location: Golden, CO, USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Driven Query Task

Hi All -- I have a unique situation

I want to execute a stored procedure for data pulled from a flat file, which I have figured out.

My question is this: I have a flat file where the data must insert several rows in the same table; the data occurs 3 times on the flat file in succession:

CONTRACT01 DATA FOR ROW 1 DATA FOR ROW 2 DATA FOR ROW 3

And it needs to be represented in a table like this:
ContID ContData
CONTRACT01 DATA FOR ROW 1
CONTRACT01 DATA FOR ROW 2
CONTRACT01 DATA FOR ROW 3

It does get a little bit more complex-- I can't do this through a simple insert; I want to use a stored procedure (for a lot of reasons; error handling, and putting data in several tables at once without having to do lots of lookups).

I have it figured out how to "loop" through this data using ActiveX Script with simple Inserts but that won't suit my needs.

Can someone enlighten me how to execute a stored procedure passing the proper segmented part of my record as a parameter to my stored procedure?

Thank you!

"A spirit with a vision is a dream with a mission"
__________________
\"A spirit with a vision is a dream with a mission\"
  #2 (permalink)  
Old June 22nd, 2006, 11:01 AM
Authorized User
 
Join Date: Oct 2003
Location: Golden, CO, USA.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I figured it out. I'm using a Data-Driven Query task and this is the script:
'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()

    DTSGlobalVariables("counter") = DTSGlobalVariables("counter") + 1

    Dim strTID
    Dim strTempVal

    strTID = DTSLookups("IDLookup").Execute (DTSSource("Col001"))

    Select Case DTSGlobalVariables("counter")
        Case 1
            DTSDestination("TST2_VALUE") = DTSSource("Col002")
            DTSDestination("TST_ID") = strTID
            strTempVal = DTSTransformstat_SkipFetch + DTSTransformstat_InsertQuery
        Case 2
            DTSDestination("TST2_VALUE") = DTSSource("Col003")
            DTSDestination("TST_ID") = strTID
            strTempVal = DTSTransformstat_SkipFetch + DTSTransformstat_InsertQuery
        Case 3
            DTSDestination("TST2_VALUE") = DTSSource("Col004")
            DTSDestination("TST_ID") = strTID
            strTempVal = DTSTransformstat_SkipFetch + DTSTransformstat_InsertQuery
        Case Else

    End Select

    If DTSGlobalVariables("counter") >= 4 Then
        strTempVal = DTSTransformStat_SkipRow
        DTSGlobalVariables("counter") = 0
    End If

    Main = strTempVal

End Function

'Adding the DTSTransformstat_SkipFetch & DTSTransformstat_InsertQuery did the trick.

"A spirit with a vision is a dream with a mission"
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Not getting Data Driven Subcription Option Mahesh N Raju Reporting Services 0 July 20th, 2007 06:06 AM
Data-Driven Subscription Markus27 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 May 12th, 2006 08:42 PM
Users selection driven search query... mat41 Classic ASP Databases 3 November 16th, 2004 03:14 AM
Data Driven Query Update Row Problem Hardache SQL Server DTS 1 June 10th, 2004 08:42 AM



All times are GMT -4. The time now is 05:09 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.