SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Urgent: Can i have dynamic mappings in Transform
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vishnu.saxena
Starting Member

3 Posts

Posted - 03/09/2012 :  04:32:02  Show Profile  Reply with Quote
Hi All,

i am new to DTS, i am facing a problem.
I have a flat file (fixed length | (pipe) delimt.) as source file, i need to read the contents of this file into SQL server 2000 table using Transform Data task. My problem is the number of columns in the Flat file are variable (based on the code that record may contain for e.g. if code is 01 then that record may hold 5 col's if code is 06 that record may contain 40 columns) my problem is i am using copy column in Transformation mapping (with maximum fields that the file can accommodate; but when the file does not contains the record having max number of columns the transformation fails.)
E.g. if File contains some thing like below
01|ABC|TestDummy|DummyRec1|DummyRec2
06|ABC|TestDummy|DummyRec1|DummyRec2......DummyRec40
it works
but it fails for below
01|ABC|TestDummy|DummyRec1|DummyRec2
05|XYZ|TestDummy|DummyRec1|DummyRec2..| |..DummyRec30
(i.e. max field length is of 30 column)

i would be greatful if you can provide any help / pointers (w.r.t creating dynamic mapping skipping the error Column not found while transform step)

Thanks

Vishnu

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/09/2012 :  11:56:09  Show Profile  Reply with Quote
you cant have file metadata changes at runtime. You need to make the columns fixed ie fix it at maximum value (40) just like what you did. for other codes they'll be taken as blanks and then add transformation to check if its blanks and if so add logic to handle them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vishnu.saxena
Starting Member

3 Posts

Posted - 03/09/2012 :  23:24:15  Show Profile  Reply with Quote
Thanks for replying..
the problem in fixing it at 40 is the transformation fails when the number of columns in file are less then 40 i.e.
File can contain lets say n types of records for e.g. length of 001 type is 10 cols. 002 has 20 cols... now there is a possibility that the file (input) contains only 001 type records hence the transformation fails as the transformation fails to get value of other columns in source file.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/10/2012 :  13:49:03  Show Profile  Reply with Quote
why dont you parse the file in script task and then count number of columns. based on column count select a branch for continuing execution which will have logic for each file. the code have to be written in activex script

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vishnu.saxena
Starting Member

3 Posts

Posted - 03/12/2012 :  00:53:44  Show Profile  Reply with Quote
Yeah had given a thought to that but as the codes (on the basis of which the branching needs to be done are user defined currently some 109 codes; creating 109 branches would not be a good idea what do you suggest ??)

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/12/2012 :  10:28:03  Show Profile  Reply with Quote
you can put those codes in a table and in dts package compare against that codes to decide the branch by populating a recordset with it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000