Urgent: Can i have dynamic mappings in Transform Data Task: please reply ASAP
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
March 9th, 2012 4:10am

The short answer is no. I think most of peoples here are talking about the SSIS instead of DTS. Even though you are using SSIS, there is no easier way to do so. Some people did that by using OLD DB task or Execute SQL task, however that's no difference to do so by using stored procedures directly.
Free Windows Admin Tool Kit Click here and download it now
March 9th, 2012 9:27am

dynamic mappings is not a strong point of SSIS. You could try something with a Script Task as source: http://microsoft-ssis.blogspot.com/2011/02/script-component-as-source.html It allows you to read a poorly formatted csv file.Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
March 9th, 2012 9:41am

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
Free Windows Admin Tool Kit Click here and download it now
March 9th, 2012 11:56am

Thanks for replying Patrick. the product is using Legacy SQL server 2000 (and have got no choice but to get this working on SQL server 2000 DTS :(, can you please give me some code example that i may take on to develop the solution, additionaly the work around that i had thought is to pre parse the file before the transformation step executes to check the number of columns in file and if the number of columns is less then what is expected by Transformation step i will insert a dummy row with dummy data & allow transformation to work, once every thing is in place i will remove the additional row from DB, i know this is not the best of the approaches but as said i dont have much work exp on DTS so really dont know how to code this thanks
March 9th, 2012 11:28pm

Thanks for replying .. i am afraid i would not be able to use the SSIS as the product is on Legacy MS SQL 2K. it would be great in case you can suggest a code example or pseudo code for what i need to do in / via VBScript. many thanks Vishnu
Free Windows Admin Tool Kit Click here and download it now
March 9th, 2012 11:30pm

Hi, Are you forced to use DTS only? If no, you may go with a simple console app and use TextReader/StreamRader and build you custom logic: read text row by row and group them by id into data tables for example. If you still want to use DTS with script task then you may search for FSO and use it for reading your text file. I hope it helps. J. There are 10 type of people. Those who understand binary and those who do not. My Blog
March 10th, 2012 1:11am

Well, if you have no choice to use the DTS, I think you can consider the following steps, (even though using SSIS) Create a table with a single column with the Varchar datatype, the length should be enough to fit the max length of the rowsLoad all records (all rows) as is into the single column tableCreate a stored procedures or any SQL script to dynamically analyze your logic and insert the record into your target table.
Free Windows Admin Tool Kit Click here and download it now
March 11th, 2012 9:21am

Hi Patrick, Yeah i am stuck with DTS only (s client dont want to pay for SSIS and considering the huge risks involved to migrate), thanks for suggestion to put all data into a single column table & then split to analyze sounds good, i was thinking to add a dummy row having max length of records and then once all done delete that row from DB. what do you guys suggest? Thanks a ton for writing..
March 12th, 2012 12:48am

Hi Janos, Yeah unfortunately i am.. :( the DTS package contains lots of other steps something like 50+ and changing the entire DTS might risk the flow of program (this DTS package is a bit complex as i am novice on DTS so feeling shaky to change the whole) but yes would do a search on FileSystemObject (i hope thats what you meant by FSO) Thanks for writing back Thanks
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2012 12:51am

Hello Vishnu, I would resolve this issue with a Script Task using VBScript to split this file onto two: one having the 01 records with 5 cols, and the other would be with 06 rows (40 cols). This way you arrive to static metadata and thus must be able to successfully set the file processing and map the data flow up. As an example please refer yourself to this post: http://www.vbforums.com/showthread.php?t=614401Arthur My Blog
March 12th, 2012 4:35pm

Hello Vishnu, I would resolve this issue with a Script Task using VBScript to split this file onto two: one having the 01 records with 5 cols, and the other would be with 06 rows (40 cols). This way you arrive to static metadata and thus must be able to successfully set the file processing and map the data flow up. As an example please refer yourself to this post: http://www.vbforums.com/showthread.php?t=614401Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2012 4:35pm

Double post: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/215a7ae5-ba3c-4aff-9a87-aa7e6217991c/#92d293fd-1c19-4922-b1fc-59dacbcd23d5Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter
March 12th, 2012 4:50pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics