August 6, 2009 at 5:52 am
I've created a package that simply executes a simple SELECT statement and I need to export the results to a Fixed Width flat file. I've setup the Data Flow task component with the OLE DB Source which holds the query, and the Flat File Destination that is set to Fixed Width and {CR}{LF} as the "header row delimiter".
When the package runs and creates the result file, ALL of the data is on ONE row, instead of each record on its own row. Keeping in mind that (I'm following a file format given to me) each record has a '$' sign as the last column to tell the program that eventually processes this file that is has reached the "end of record".
Ive tried different "header row delimiters" but all records end up being on one line in the result file. Can anyone offer any assistance as to why it would do this?
--edit
I've uploaded a few screenshots
General - The general config of the results file
Preview - The Preview pane of the file, the results look correct, however the actual flat file is a different story
Mappings - Screenshot of the mappings pane
Results - Raw Text file export. You can see the "End of Record" character (which is coming from the SQL Query) and each record is definitely not on its own row. I've had to obfuscate the data for legal reasons.
Below is the query
SELECT
' ' AS [filler_0] ,
s.[SBRDISTN] AS [district] ,
' ' AS [filler_1] ,
s.[SBRCSCHL] AS [schoolCode] ,
' ' AS [filler_2] ,
SUBSTRING(s.[SBRLAST] , 1 , 17) AS [lastName] ,
' ' AS [filler_3] ,
SUBSTRING(s.[SBRFIRST] , 1 , 12) AS [firstName] ,
' ' AS [filler_4] ,
LEFT(s.[SBRMIDDLE] , 1) AS [MI] ,
' ' AS [filler_5] ,
RIGHT('00' + CAST(MONTH(s.[SBRBIRTH]) AS VARCHAR) , 2) + RIGHT('00' + CAST(DAY(s.[SBRBIRTH]) AS VARCHAR) , 2)
+ RIGHT('0000' + CAST(YEAR(s.[SBRBIRTH]) AS VARCHAR) , 4) AS [birthDate] ,
' ' AS [filler_6] ,
LEFT(s.[SBRSEX] , 1) AS [gender] ,
LEFT(s.[SBRRACE] , 1) AS [race] ,
' ' AS [filler_7] ,
LEFT(s.[SBRSTDT] , 10) AS [stdtNum] ,
' ' AS [filler_8] ,
' ' AS [classCode] ,
' ' AS [filler_9] ,
LEFT(s.[SBREXNBR] , 10) AS [alias] ,
' ' AS [filler_10] ,
'$' AS [endOfRecord]
FROM
[dbo].[SBR] AS s
August 6, 2009 at 6:22 am
nevermind...
I didn't see the other "flat file type" called "Fixed With with Row Delimiters"
I feel dumb now.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply