Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing text file using parameters

Status
Not open for further replies.
balistikb

balistikb

Technical User
Nov 12, 2002
177
US
I am trying to use DTS to import a text file into an access table. This works fine, but now I want to import the records from that text file only if it doesn't already exist in the table. Is this possible in the transformation?
The table key is the primary key.
Here is what I got in the transformation:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

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


Dim Grade
Grade=DTSSource("Col013")

' Converting numerical grade to show as pass or fail

if Grade >79 then
Grade="Pass"
else Grade = "Fail"
end if

DTSDestination("UserData14") = DTSSource("Col097")
DTSDestination("UserData13") = DTSSource("Col096")
DTSDestination("UserData12") = DTSSource("Col095")
DTSDestination("UserData11") = DTSSource("Col094")
DTSDestination("UserData10") = DTSSource("Col093")
DTSDestination("UserData9") = DTSSource("Col092")
DTSDestination("UserData8") = DTSSource("Col091")
DTSDestination("UserData7") = DTSSource("Col090")
DTSDestination("UserData6") = DTSSource("Col089")
DTSDestination("UserData5") = DTSSource("Col088")
DTSDestination("UserData4") = DTSSource("Col087")
DTSDestination("UserData3") = DTSSource("Col086")
DTSDestination("UserData2") = DTSSource("Col085")
DTSDestination("UserData1") = DTSSource("Col084")
DTSDestination("UserData0") = DTSSource("Col083")
DTSDestination("GRADE") = Grade
DTSDestination("NET") = DTSSource("Col081")
DTSDestination("ADJERRORS") = DTSSource("Col080")
DTSDestination("ERRORS") = DTSSource("Col079")
DTSDestination("GROSS") = DTSSource("Col078")
DTSDestination("SCORE30") = DTSSource("Col077")
DTSDestination("TOPIC30") = DTSSource("Col076")
DTSDestination("SCORE29") = DTSSource("Col075")
DTSDestination("TOPIC29") = DTSSource("Col074")
DTSDestination("SCORE28") = DTSSource("Col073")
DTSDestination("TOPIC28") = DTSSource("Col072")
DTSDestination("SCORE27") = DTSSource("Col071")
DTSDestination("TOPIC27") = DTSSource("Col070")
DTSDestination("SCORE26") = DTSSource("Col069")
DTSDestination("TOPIC26") = DTSSource("Col068")
DTSDestination("SCORE25") = DTSSource("Col067")
DTSDestination("TOPIC25") = DTSSource("Col066")
DTSDestination("SCORE24") = DTSSource("Col065")
DTSDestination("TOPIC24") = DTSSource("Col064")
DTSDestination("SCORE23") = DTSSource("Col063")
DTSDestination("TOPIC23") = DTSSource("Col062")
DTSDestination("SCORE22") = DTSSource("Col061")
DTSDestination("TOPIC22") = DTSSource("Col060")
DTSDestination("SCORE21") = DTSSource("Col059")
DTSDestination("TOPIC21") = DTSSource("Col058")
DTSDestination("SCORE20") = DTSSource("Col057")
DTSDestination("TOPIC20") = DTSSource("Col056")
DTSDestination("SCORE19") = DTSSource("Col055")
DTSDestination("TOPIC19") = DTSSource("Col054")
DTSDestination("SCORE18") = DTSSource("Col053")
DTSDestination("TOPIC18") = DTSSource("Col052")
DTSDestination("SCORE17") = DTSSource("Col051")
DTSDestination("TOPIC17") = DTSSource("Col050")
DTSDestination("SCORE16") = DTSSource("Col049")
DTSDestination("TOPIC16") = DTSSource("Col048")
DTSDestination("SCORE15") = DTSSource("Col047")
DTSDestination("TOPIC15") = DTSSource("Col046")
DTSDestination("SCORE14") = DTSSource("Col045")
DTSDestination("TOPIC14") = DTSSource("Col044")
DTSDestination("SCORE13") = DTSSource("Col043")
DTSDestination("TOPIC13") = DTSSource("Col042")
DTSDestination("SCORE12") = DTSSource("Col041")
DTSDestination("TOPIC12") = DTSSource("Col040")
DTSDestination("SCORE11") = DTSSource("Col039")
DTSDestination("TOPIC11") = DTSSource("Col038")
DTSDestination("SCORE10") = DTSSource("Col037")
DTSDestination("TOPIC10") = DTSSource("Col036")
DTSDestination("SCORE9") = DTSSource("Col035")
DTSDestination("TOPIC9") = DTSSource("Col034")
DTSDestination("SCORE8") = DTSSource("Col033")
DTSDestination("TOPIC8") = DTSSource("Col032")
DTSDestination("SCORE7") = DTSSource("Col031")
DTSDestination("TOPIC7") = DTSSource("Col030")
DTSDestination("SCORE6") = DTSSource("Col029")
DTSDestination("TOPIC6") = DTSSource("Col028")
DTSDestination("SCORE5") = DTSSource("Col027")
DTSDestination("TOPIC5") = DTSSource("Col026")
DTSDestination("SCORE4") = DTSSource("Col025")
DTSDestination("TOPIC4") = DTSSource("Col024")
DTSDestination("SCORE3") = DTSSource("Col023")
DTSDestination("TOPIC3") = DTSSource("Col022")
DTSDestination("SCORE2") = DTSSource("Col021")
DTSDestination("TOPIC2") = DTSSource("Col020")
DTSDestination("SCORE1") = DTSSource("Col019")
DTSDestination("TOPIC1") = DTSSource("Col018")
DTSDestination("NTOPICS") = DTSSource("Col017")
DTSDestination("SCOREADV") = DTSSource("Col016")
DTSDestination("SCOREINT") = DTSSource("Col015")
DTSDestination("SCOREBEG") = DTSSource("Col014")
DTSDestination("SCORETOT") = DTSSource("Col013")
DTSDestination("TIMEELAP") = DTSSource("Col012")
DTSDestination("TIMESTRT") = DTSSource("Col011")
DTSDestination("DBDATE") = DTSSource("Col010")
DTSDestination("CURR_DATE") = DTSSource("Col009")
DTSDestination("TEST") = DTSSource("Col008")
DTSDestination("STSFILE") = DTSSource("Col007")
DTSDestination("DATFILE") = DTSSource("Col006")
DTSDestination("ID") = DTSSource("Col005")
DTSDestination("LAST") = DTSSource("Col004")
DTSDestination("FIRST") = DTSSource("Col003")
DTSDestination("parentKey") = DTSSource("Col002")
DTSDestination("tableKey") = DTSSource("Col001")

Main = DTSTransformStat_OK
End Function



 
Sort by date Sort by votes
balistikb

To the best of my knowledge, there is no way to use DTS Import to only import records that do not already exist.

However, you could develop a 2 step process...

Step 1 - DTS Import to a "holding" Table in Access
Step 2 - An Access Update Query that loads the data
from the "holding" Table to the actual Table.

Another possibility: If the text file contains all data, you could first delete all records in the Access table via a DTS SQL Task, then run the Import.

Hope that helps.

 
Upvote 0 Downvote

You may want to try using a "Data Driven Query Task" with DTS. It requires a little more setup work, but when using the Insert Query you can perform an IF EXISTS test first. I know this works with a SQL Server database so I'm pretty sure it will work with Access as well..
 
Upvote 0 Downvote
You have to be careful this way. If a record was updated, it will exists, and therefor not be updated (as compared to a new record being added because it didn't exist prior to import). This is important if the source data rows contail changes versus additions solely. I'm doing something similar where I have an application on a Linux box that dumps itself every night, and then I mirror the data on a SQL box for reporting via Crystal. In my situation, the data changes more than new records being added to the source system. In my scenario, I first drop all the indexes and the table and then recreate them via a SQL task in a DTS pachakge. Additionally, each table import is it's own package, and each package is called as a child by a master, Parent package in the order I desired.

Hope that helps.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

jeffwest21
  • Locked
  • Question
BCP Out file with double quotes
Replies
1
Views
691
gk53
gk53
ItHurtsWhenIThink
  • Locked
  • Question
Using Distinct and count in same query???
Replies
4
Views
603
gk53
gk53
sa0309
  • Locked
  • Question
Input Prompt parameters
Replies
0
Views
432
sa0309
sa0309
arodri
  • Locked
  • Question
MSVisual Studio - DTSX Package creating new text file
Replies
0
Views
455
arodri
arodri
MICKI0220
  • Locked
  • Question
Overflow problem when importing to sql 2008
Replies
2
Views
355
drlex
drlex

Part and Inventory Search

Sponsor

Back
Top