October 21, 2013 at 11:16 am
I am working on a project that requires me to add 3 different record types to one file. Each of the records has varying column names and record sizes. For example, the first record has a length of 1307 characters, the second record has a length of 1467, and the third record has a length of 1390. This would be easy if I could just put the type 1 records first, then copy, without overwriting the data, the type 2 and type 3 records respectively after the first record types were inserted, into that same file.
Herein lies the problem. They need to be sorted by a PO#. So in essence, a record type 1 needs to be inserted first, with the first PO#, and then the type 2 record with that same PO#, and finally the type 3 record using the same PO# as the first two, before inserting the type 1 record for the next PO#. I tried to populate this into one file by setting up a stored procedure for each record type that would create one column (Column0) with all the data, and required spacing for each record, and insert it into a temporary table. I then run a Data Flow query that would sort by PO# and then record type. I set the flat file destination column (Column0) length to the highest of the 3 record types, which is 1467. This works when I look at the data from the query, but once inserted into the file, all three records have a length of 1467. This would only work for the type 2 record. I need to be able to trim off any spaces for the other two record types, type 1 and type 3. I cannot seem to get those extra spaces for type 1 and type 3 records to be trimmed.
Is there a way to set this up in SSIS, so that each record type has its respective lengths, and still be sorted by the PO# and then record type, into one file?
October 21, 2013 at 12:41 pm
It sounds like you are very close to success - well done on getting so far.
Are you able to determine at exactly which point in the dataflow that the padding spaces are being added? Perhaps by using data viewers.
What, exactly, are you using as the datatype of the single column in the dataflow?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2013 at 12:58 pm
I copied the data from the Data Viewer, between the OLE DB Source, and the Flat File Destination, and, using ConText, I am able to determine that the column length for each record type is correct, so I could only conclude that it is padding the extra spaces in at the insert data into file process. The Column0 field, that I am putting the row of data into, is in String format.
October 21, 2013 at 1:03 pm
miles_lesperance (10/21/2013)
I copied the data from the Data Viewer, between the OLE DB Source, and the Flat File Destination, and, using ConText, I am able to determine that the column length for each record type is correct. The Column0 field, that I am putting the row of data into, is in String format.
Are you saying that the additional spaces are being added when the Flat File Destination is written?
--Edit: aha, I see that your answer is yes 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2013 at 1:09 pm
In your flat file connection manager, which 'Format' are you using?
I would suggest trying out 'Delimited' with 'None' as your Text qualifier and {CR}{LF} as your column delimiter.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 21, 2013 at 1:16 pm
Yes, it appears as though the space is being added when written to the Flat File Destination. Right now, my file format is Ragged Right. I will try your recommendation of a Delimited file, with no text qualifier, and carriage return/line feed as my column delimiter. I will keep you posted if that works, or not.
October 21, 2013 at 1:21 pm
That worked!! 😀 Switching it from a Ragged Right to Delimited Flat File Destination has prevented the data from being padded with extra trailing spaces, for the type 1 and type 3 records, when inserting into a Flat File Destination.
Thank you very much!!
October 21, 2013 at 1:30 pm
Great! Thanks for posting back.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply