Skip to main content

Esportazione dati da SQL a file posizione fissa


Exporting data from SQL Server to Fixed Position Flat file

Just as usual, this post is kind of “Bi-product” of my attempt to create a demo for another post in which I need to use fixed position file. At first I tried to create a fixed position file using BCP. BCP has query out option to export query results into a file, but I was unable to produce the fileformat which uses fixed position. I actually even tried to use format file to generate required format but I ran into many issues so finally I thought that it is more important to get results than means to achieve it (at least it is true in this case Open-mouthed smile) and I fired up Visual Studio to use SSDT.

Actually it is very easy to generate a fixed position file using SSIS. All you have to create is a simple Data Flow task and then add a SQL Server as source and a flat file as destination into that task. Only thing to be sure is configuring Flat File destination settings.

FlatFileConnection Details Preview

In Flat File Connection Manager settings, goto General tab and make sure that FORMAT is “Ragged Right” not “Fixed Width”. What’s confusing is if you want to add row delimiter in the end then you have to use Ragged Right not Fixed Width. And that’s it !!

Simply run the package and you have fixed width flat file for your all Devil experiments  …

I have attached sample SSIS project created with SSDT to give an idea. Of course you will need to modify this project to meet your requirements.

That’s it for now…

It’s Just A Thought … Peace

Gaurang Sign



I actually just did this last week. I wrote a function that does this then for each field I call the function.

Here is the function

/* USE [Newton-Dev] GO /****** Object: UserDefinedFunction [dbo].[CharPad] Script Date: 1/10/2015 11:38:27 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Script : Character Padding Function; Assist with fixed width file creation Version : 1.0 (01/08/2015) Author : Jeffery Williams */ ALTER FUNCTION [dbo].[CharPad] ( @Input VARCHAR(255) ,@OutputWidth INT ,@OutputAlign VARCHAR(5) ,@PadCharacter CHAR(1) ) RETURNS VARCHAR(255) AS BEGIN DECLARE @Output VARCHAR(255) DECLARE @InputWidth INT SET @InputWidth = LEN(@Input) IF @InputWidth > @OutputWidth BEGIN IF @OutputAlign = 'LEFT' BEGIN SET @Output = LEFT(@Input,@OutputWidth) END IF @OutputAlign = 'RIGHT' BEGIN SET @Output = RIGHT(@Input,@OutputWidth) END END IF @InputWidth < @OutputWidth BEGIN IF @OutputAlign = 'RIGHT' BEGIN SET @Output = REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) + @Input END IF @OutputAlign = 'LEFT' BEGIN SET @Output =@Input+ REPLICATE(@PadCharacter, @OutputWidth - @InputWidth ) END END IF @InputWidth = @OutputWidth SET @Output = @Input RETURN (@Output) END

Here is the query that calls the function and provides the fixed width output:

SELECT --dbo.CharPad (DeltaLineId,8,'LEFT',' '), dbo.CharPad (DeltaElgId,8,'LEFT',' ') dbo.CharPad (CARRIER_ID,6,'LEFT',' ') ,dbo.CharPad (GROUP_NUM,7,'RIGHT','0') ,dbo.CharPad (LEFT('0000' + SUB_GROUP_ID, 4),9,'LEFT',' ') ,dbo.CharPad (SVC_TYPE,1,'LEFT',' ') ,dbo.CharPad (FILLER_1,1,'LEFT',' ') ,dbo.CharPad (FILLER_2,5,'LEFT',' ') ,dbo.CharPad (RATE_CODE,2,'LEFT',' ') ,dbo.CharPad (FILLER_3,1,'LEFT',' ') ,dbo.CharPad (ELIG_CODE,1,'LEFT',' ') ,dbo.CharPad (EFF_DATE,8,'LEFT',' ') ,dbo.CharPad (TERM_DATE,8,'LEFT',' ') ,dbo.CharPad (SUBSC_SSN,9,'LEFT',' ') ,dbo.CharPad (INDIV_SSN,9,'LEFT',' ') ,dbo.CharPad (CHNG_SSN,9,'LEFT',' ') ,dbo.CharPad (REL_CODE,2,'LEFT',' ') ,dbo.CharPad (HIRE_DATE,8,'LEFT',' ') ,dbo.CharPad (DOB,8,'LEFT',' ') ,dbo.CharPad (REL_TYPE,1,'LEFT',' ') ,dbo.CharPad (FIRST_NAME,24,'LEFT',' ') ,dbo.CharPad (MID_NAME,24,'LEFT',' ') ,dbo.CharPad (LAST_NAME,24,'LEFT',' ') ,dbo.CharPad (GENDER,1,'LEFT',' ') ,dbo.CharPad (POP_DESC,5,'LEFT',' ') ,dbo.CharPad (ADR_LINE_1,30,'LEFT',' ') ,dbo.CharPad (ADR_LINE_2,30,'LEFT',' ') ,dbo.CharPad (CITY,30,'LEFT',' ') ,dbo.CharPad ([STATE],2,'LEFT',' ') ,dbo.CharPad (COUNTY_CODE,3,'LEFT',' ') ,dbo.CharPad (COUNTRY_CODE,3,'LEFT',' ') ,dbo.CharPad (ZIP,5,'LEFT',' ') ,dbo.CharPad (ZIP_EXT,4,'LEFT',' ') ,dbo.CharPad (FILLER_4,21,'LEFT',' ') ,dbo.CharPad (USER_DEFINED,30,'LEFT',' ') ,dbo.CharPad (WAIT_PERIOD,1,'LEFT',' ') ,dbo.CharPad (CAID,9,'RIGHT','0') ,dbo.CharPad (FILLER_5,9,'LEFT',' ') FROM export.DeltaLine */