Selective Import using DTS

[jcoleman]

Hi guys,

I'm using SQL 2000, and wrote a vb script to import data from a fixed width text file.

if DTSSource("Col008") = "M" then 
DTSDestination("On Flyer") = DTSSource("Col020")
DTSDestination("GST") = DTSSource("Col019")
...
	Main = DTSTransformStat_OK
else
	Main = DTSTransformStat_SkipRow
End If

The first record with an M in column 8 imports ok, but the only other records that import are the ones were there are more than two M records in a row, and it imports the second one but not the first one.

Like this

record one 'M' is imported
record two 'P' not imported which is correct
record three 'M' not imported (not good)
record four 'M' is imported

I hope I explain that correctly.

Any ideas?

thanks jc

 

[JVZ]

It could be related to the data you are trying to import. Could you please provide some sample data ?

 

[jcoleman]

Thanks for the reply JVZ

Here is a sample of some of the data, I can't see anything wrong with it. If I pull it up in MS Excel I get 315 lines from this file that match my query, but my SQL script only imports 164 lines.

The lines that are bold are the ones not being imported.

Let me know if you see anything....Thanks,

05226700990524712000001M00014             315300660C225     1    799    799-Y      0YN
05226700990524712000001X00014     64-     56-
05226700990524712000001T00014    919-      0 
05226700990524712000001P0001451    919-
[b]05226700990524912000001M00022             327968220C303     4     99    396 Y      0YN[/b]
05226700990524912000001M00022             328201770C319     1     79     79 Y      0YN
05226700990524912000001X00022     38      34 
05226700990524912000001T00022    547       0 
05226700990524912000001P0002251    547 
[b]05226700990525112000001M00022             427507110D720     1    699    699 Y      0YN[/b]
05226700990525112000001X00022     56      49 
05226700990525112000001T00022    804       0 
05226700990525112000001P0002251    804 
[b]05226700990525212000001M00022             360004550B812     3    309    927 N      0YN[/b]
05226700990525212000001M00022             361003220B805     3    944   2832 N      0YN
05226700990525212000001X00022      0     263 
05226700990525212000001T00022   4022       0 
05226700990525212000001P0002251   4022 
[b]05226700990525312000001M00014             319406000C228     1    199    199-Y      0YN[/b]
05226700990525312000001M00014             320350000C315     2    299    598-Y      0YN
05226700990525312000001M00014             330344990C506     1    269    269-Y      0YN

 

[MDXer]

Why not load the data into a stage table then move your records using T-SQL. Seems like it would be easier to trouble shoot.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[JVZ]

JC –

The problem is with the data. You defined the file as fixed-width which also means each record is a fixed number of characters long.

However according to the sample data provided the first record is 86 characters, and then the second records is only 45 characters long. So its looks like the Text File (Source) Connection is making each record the same length of 86 characters by merging rows. For example, the second row is 45 characters long so it takes 41 characters (86-45) from the third row, as so on. This is not good, since now the rows are merged now.

A couple of options for a work around are:

1. As MDXer suggested load the data into a stage table then move your records using T-SQL.
2. Create a preprocessor step that “cleans” the data before loading, for example:

[blue]Dim[/blue] objFileSystem 
[blue]Dim[/blue] objInputSteam
[blue]Dim[/blue] objOutputSteam 
[blue]Dim[/blue] strCleanedDataFileName
[blue]Dim[/blue] strInputFileName
[blue]Dim[/blue] strInputString
[blue]Dim[/blue] strStringLength

[blue]Const[/blue] FOR_READING		= 1
[blue]Const[/blue] MAX_RECORD_LENGTH  = 86

strCleanedDataFileName = [teal]"C:\test_cleaned.txt"[/teal]
strInputFileName	   = [teal]"C:\test.txt"[/teal]

[blue]Set[/blue] objFileSystem	 = [navy]CreateObject[/navy]([teal]"Scripting.FileSystemObject"[/teal])

[blue]Set[/blue] objOutputSteam    = objFileSystem.CreateTextFile(strCleanedDataFileName, [blue]TRUE[/blue])
[blue]Set[/blue] objInputSteam	 = objFileSystem.OpenTextFile (strInputFileName ForReading)

[blue]Do Until[/blue] objInputSteam.AtEndOfStream

  strInputString    = objInputSteam.ReadLine
  strStringLength   = [navy]Len[/navy](strInputString)
  
 [green] ' If the length of the input string is less than MAX_RECORD_LENGTH then fill the remaining
  ' characters with spaces. This will insure that each record is the same number of characters.[/green]
  objOutputSteam.WriteLine strInputString & [navy]space[/navy](MAX_RECORD_LENGTH - strStringLength)
 
[blue]Loop[/blue]

[blue]Set[/blue] objOutputSteam  = [blue]Nothing[/blue]  
[blue]Set[/blue] objInputSteam   = [blue]Nothing[/blue]  
[blue]Set[/blue] objFileSystem   = [blue]Nothing[/blue]

Hope this helps.

 

[jcoleman]

Thanks, JVZ

Now, sorry for being such a newby; but what do I use to write this code and how do I run this code?

Training is a very low priorty around here so I pretty much have to wing everything that I'm doing.

Again sorry for being such an idiot.

jc

 

[JVZ]

>>...but what do I use to write this code
I wrote this in a ActiveX Task

>>...and how do I run this code?

Place the above code in ActiveX Task before the data load task


You may want to visit

http://www.SqlDTS.com

to find out more information on DTS.

Hope this helps.

 

[jcoleman]

Thanks, JVZ

It works great.

jc