SQLTeam.com | Weblogs | Forums

Export to Fixed Format txt file using SSIS with different header widths, detail widths and trailer widths


#1

Can someone help me with this. Time is of a essence. I tried to use ragged right but I am unable to create all the columns I need. My query is

Declare @StartDate as DateTime, @EndDate as DateTime

select @StartDate = ParameterStartDate, @EndDate = ParameterEndDate 
from dbo.LkupExtractControl
where ExtractName = 'APORS'

SELECT CAST([APORSFacilityId] as varchar(4)) [APORS Facility ID] 
	   ,CAST([FacilityName] as varchar(40)) [Facility Name]
	   ,CAST([FacilityAddress] as varchar(40)) [Facility Street Address]
	   ,CAST([FacilityCity] as varchar(20)) [Facility City]
	   ,CAST([FacilityZipCode] as varchar(5)) [Facility Zip Code]
	   ,CAST([APORSContactPerson] as varchar(40)) [Contact Person]
	   ,CAST(REPLACE([APORSContactPhone],'-','') as varchar(10)) [Contact's Telephone Number]
	   , (Select CAST (replace(convert(varchar(10), [ParameterStartDate], 110),'-','') as varchar(8))
		  from  dbo.LkupExtractControl
		  where ExtractName = 'APORS') [Period covered first day]
	   ,(Select CAST (replace(convert(varchar(10), [ParameterEndDate], 110),'-','') as varchar(8))
		  from  dbo.LkupExtractControl
		  where ExtractName = 'APORS') [Last Day] 
	   ,CAST('' as varchar(5)) Filler1
	   ,CAST('' as varchar(5)) Filler2
	   ,CAST('' as varchar(5)) Filler3
	   ,CAST('' as varchar(5)) Filler4
	   ,CAST('' as varchar(5)) Filler5
	   ,CAST('' as varchar(5)) Filler6
	   ,CAST('' as varchar(5)) Filler7
from [LkupAPORSFacility]
Where [Site] = @Site
UNION ALL
SELECT  Distinct CAST(replace(Convert(varchar(10),p.DateOfBirthDate,110),'-','') as varchar(8)) as [Patient Birth Date]
		,CAST(p.GenderName as varchar(1)) [Patient Sex]
		,CAST(p.AddressText as varchar(30)) [Patient Street Address]
		,CAST(p.CityName as varchar(20))  [Patient City]
		,CAST(p.StateName as varchar(2)) [Patient State]
		,CAST(p.ZipCode as varchar(5)) [Patient Zip Code]
		,CAST(replace(Convert(varchar(10),e.AdmitDate,110),'-','') as varchar(8)) [Admission date]
		,CAST(replace(Convert(varchar(10),e.DischargeDate,110),'-','') as varchar(8)) [Discharge Date]
		,CAST(case when DateDiff(month,p.DateofBirthDate,e.DischargeDate) < 1 then 1 else 2 end as varchar(1)) as [Type of Admission]
		,CAST(ltrim(d.DiagnosisCode) as varchar(7)) [Diagnosis Code]
		,CAST(ltrim(p.LastName) as varchar(20)) [Patient last name]
		,CAST(ltrim(p.FirstName) as varchar(20)) [Patient first name]
		,CAST(ltrim(e.EncounterNumber) as varchar(20)) [Patient ID number]
		,CAST(ltrim(p.MedicalRecordNumber) as varchar(20)) [Patient medical record number]
		,CAST(ltrim(isnull(dp.LastName,' ')) as varchar(20)) as MotherLastName
		,CAST(ltrim(isnull(dp.FirstName,' ')) as varchar(20)) as MotherFirstName
from DwEncounter e 
inner join DwPatient p  
on e.patientkey = p.patientkey 
inner join LkupAPORSFacility laf
ON (e.FacilityKey = laf.FacilityKey)
left outer join DwPatient dp  
on (dp.medicalrecordnumber = right(e.MothersMedicalRecordNumber,9) and dp.facilitykey = e.facilitykey)
left outer join DwEncounterDiagnosises d 
on e.encounterkey = d.encounterkey
Where DischargeDate >= @StartDate and Dischargedate <= @EndDate 
and d.DiagnosisCode like 'Q%' 
and d.DiagnosisTypeKey <> 171 --exclude admitdiagnosis
and (Case When (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,1,convert(varchar(10),p.DateOfBirthDate,120)))  -- >= 1 year
			 then Case when (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,(datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) )), convert(varchar(10),p.DateOfBirthDate,120)))
                          then (datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) )) 
                       else ((datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) ))-1) 
				  end
		  else 0
	end)  < 2
and (p.StateName = 'IL')	
and laf.Site = @Site
UNION ALL
select CAST([APORSFacilityId] as varchar(12)) [APORS Facility ID]
       ,CAST(count(1) as varchar(5)) [Number of Records]
	   ,CAST('' as varchar(13)) Filler1
	   ,CAST('' as varchar(13)) Filler2
	   ,CAST('' as varchar(13)) Filler3
	   ,CAST('' as varchar(13)) Filler4
	   ,CAST('' as varchar(13)) Filler5
	   ,CAST('' as varchar(13)) Filler6
	   ,CAST('' as varchar(13)) Filler7 
	   ,CAST('' as varchar(13)) Filler8
	   ,CAST('' as varchar(13)) Filler9
	   ,CAST('' as varchar(13)) Filler10
	   ,CAST('' as varchar(13)) Filler11
	   ,CAST('' as varchar(12)) Filler12
	   ,CAST('' as varchar(12)) Filler13
	   ,CAST('' as varchar(13)) Filler14
from DwEncounter e 
inner join DwPatient p  
on e.patientkey = p.patientkey 
inner join LkupAPORSFacility laf
ON (e.FacilityKey = laf.FacilityKey)
left outer join DwPatient dp  
on (dp.medicalrecordnumber = right(e.MothersMedicalRecordNumber,9) and dp.facilitykey = e.facilitykey)
left outer join DwEncounterDiagnosises d 
on e.encounterkey = d.encounterkey
Where DischargeDate >= @StartDate and Dischargedate <= @EndDate 
and d.DiagnosisCode like 'Q%' 
and d.DiagnosisTypeKey <> 171 --exclude admitdiagnosis
and (Case When (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,1,convert(varchar(10),p.DateOfBirthDate,120)))  -- >= 1 year
			 then Case when (convert(varchar(10),e.DischargeDate,120) >= dateadd(yy,(datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) )), convert(varchar(10),p.DateOfBirthDate,120)))
                          then (datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) )) 
                       else ((datediff(yy,convert(varchar(10),p.DateOfBirthDate,120) ,convert(varchar(10),e.DischargeDate,120) ))-1) 
				  end
		  else 0
	end)  < 2
and (p.StateName = 'IL')	
and laf.Site = @Site
group by CAST([APORSFacilityId] as varchar(12))

#2

more info please, what columns are you unable to create? Are you getting an error message (post it here)?