February 28, 2018 at 7:15 am
I have a 132, 180, 18, and 34 column records and I need to write them to a flat file destination. Each record consists of one character string of those lengths. This is a header, detail, special, and trailer record scenario. In the Advance tab of the Flat File Connection Manager it wants an OutputColumnWidth. I have set it to 180 because that's the longest record but my client is rejecting the file because the header record is too long. I have searched high and low and I can't find anything that will tell me how to write variable length records to a file unless the file is delimited. I'm not really that C# savvy except for the relatively simple scripting I do in SSIS. Could I do it with C# or some other output mechanism?
"Beliefs" get in the way of learning.
February 28, 2018 at 7:35 am
You treat your output as a single field and create a delimited file. The delimiter separates fields and there are always one fewer delimiters than fields, so if you are only outputting one field, you have one fewer delimiters or zero delimiters.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 28, 2018 at 7:41 am
Robert Frasca - Wednesday, February 28, 2018 7:15 AMI have a 132, 180, 18, and 34 column records and I need to write them to a flat file destination. Each record consists of one character string of those lengths. This is a header, detail, special, and trailer record scenario. In the Advance tab of the Flat File Connection Manager it wants an OutputColumnWidth. I have set it to 180 because that's the longest record but my client is rejecting the file because the header record is too long. I have searched high and low and I can't find anything that will tell me how to write variable length records to a file unless the file is delimited. I'm not really that C# savvy except for the relatively simple scripting I do in SSIS. Could I do it with C# or some other output mechanism?
One option would be to create four separate files and then put them all together at the end (a simple DOS COPY command can do this).
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
February 28, 2018 at 9:16 am
Okay, I resolved this in such a simple fashion that I'm embarrassed that I didn't think of it sooner. Each record has a type number, i.e. 1 for header, 2 for detail etc.
SELECT RRE,
ClaimantID,
RowType,
TextData
FROM ( SELECT
RRE,
ClaimantID,
RowType,
CASE (RowType)
WHEN 1 THEN SUBSTRING(TextData,1,132)
WHEN 2 THEN SUBSTRING(TextData,1,180)
WHEN 3 THEN SUBSTRING(TextData,1,18)
WHEN 4 THEN SUBSTRING(TextData,1,32)
END AS TextData
FROM dbo.TMP_ClaimValidationFinalTextData
) AS Y
ORDER BY ClaimantID, RowType
;
"Beliefs" get in the way of learning.
February 28, 2018 at 9:22 am
Robert Frasca - Wednesday, February 28, 2018 9:16 AMOkay, I resolved this in such a simple fashion that I'm embarrassed that I didn't think of it sooner. Each record has a type number, i.e. 1 for header, 2 for detail etc.
SELECT RRE,
ClaimantID,
RowType,
TextData
FROM ( SELECT
RRE,
ClaimantID,
RowType,
CASE (RowType)
WHEN 1 THEN SUBSTRING(TextData,1,132)
WHEN 2 THEN SUBSTRING(TextData,1,180)
WHEN 3 THEN SUBSTRING(TextData,1,18)
WHEN 4 THEN SUBSTRING(TextData,1,32)
END AS TextData
FROM dbo.TMP_ClaimValidationFinalTextData
) AS Y
ORDER BY ClaimantID, RowType
;
Nice and simple.
Just wondering ... did you choose SUBSTRING() rather than LEFT() for any particular reason?
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
February 28, 2018 at 10:05 am
Phil Parkin - Wednesday, February 28, 2018 9:22 AMRobert Frasca - Wednesday, February 28, 2018 9:16 AMOkay, I resolved this in such a simple fashion that I'm embarrassed that I didn't think of it sooner. Each record has a type number, i.e. 1 for header, 2 for detail etc.
SELECT RRE,
ClaimantID,
RowType,
TextData
FROM ( SELECT
RRE,
ClaimantID,
RowType,
CASE (RowType)
WHEN 1 THEN SUBSTRING(TextData,1,132)
WHEN 2 THEN SUBSTRING(TextData,1,180)
WHEN 3 THEN SUBSTRING(TextData,1,18)
WHEN 4 THEN SUBSTRING(TextData,1,32)
END AS TextData
FROM dbo.TMP_ClaimValidationFinalTextData
) AS Y
ORDER BY ClaimantID, RowType
;Nice and simple.
Just wondering ... did you choose SUBSTRING() rather than LEFT() for any particular reason?
I was thinking about strings and SUBSTRING was the first to come to mind. Is LEFT more efficient?
"Beliefs" get in the way of learning.
February 28, 2018 at 11:34 am
Robert Frasca - Wednesday, February 28, 2018 10:05 AMPhil Parkin - Wednesday, February 28, 2018 9:22 AMNice and simple.
Just wondering ... did you choose SUBSTRING() rather than LEFT() for any particular reason?I was thinking about strings and SUBSTRING was the first to come to mind. Is LEFT more efficient?
I doubt that there is any difference in terms of performance (though I have not checked that).
I mentioned it because
LEFT(TextData,132)
is equivalent to
SUBSTRING(TextData,1,132)
and requires seven fewer characters and one fewer function argument, making it somewhat easier on the eye (in my opinion).
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply