Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vishnu.saxena
Starting Member
3 Posts |
 Posted - 2012-03-09 : 04:32:02
|
| 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 below01|ABC|TestDummy|DummyRec1|DummyRec206|ABC|TestDummy|DummyRec1|DummyRec2......DummyRec40it worksbut it fails for below01|ABC|TestDummy|DummyRec1|DummyRec205|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
52326 Posts |
Posted - 2012-03-09 : 11:56:09
|
| 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
vishnu.saxena
Starting Member
3 Posts |
Posted - 2012-03-09 : 23:24:15
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-10 : 13:49:03
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
vishnu.saxena
Starting Member
3 Posts |
Posted - 2012-03-12 : 00:53:44
|
| 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-12 : 10:28:03
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Urgent: Can i have dynamic mappings in Transform