This one is a bit off for me. I've had to make SSIS exports kick out in specific ways such as how a number is returned or how many spaces a column returned every time regardless of how long the actual return was. Here's a few things I've put in use that seemed to work out well in this situation. If you all have any others I don't have here that are more common or a better fit, I'm always interested adding something new to my list of tools.
Two of these were created by Igor Nikiforov. They are included at the bottom of this page. They need to be added before this script will work. If you take nothing else from this post, please visit Igor's page and look at a few of his User Defined Functions. These are very useful if you're background isn't strong into coding.
The original query is a simple select from adventure works.
select
addressid, addressline1, addressline2, city, StateProvinceID, postalcode, modifieddate
from AdventureWorks2012.Person.Address
These are a few of the conversions we used to get the outputs to fit as we needed to match an older method.
select
dbo.padl(addressid,10,'0') as AddressID
,left(addressline1 + space (40),40) as AddressLine1
,Case when addressline2 is null then '' else addressline2 end as AddressLine2
,isnull(city,'No City Listed') as City
,dbo.padl(StateProvinceID,3,'0') as StateProvinceID
,dbo.padr(convert(char(15),postalcode), 15, ' ') as ZipCode
,convert(varchar,ModifiedDate,110) as Date
,convert(varchar,ModifiedDate,108) as Time
from AdventureWorks2012.Person.Address
order by convert(varchar,ModifiedDate,112) desc, convert(varchar,ModifiedDate,108) desc
/****** Object: UserDefinedFunction [dbo].[PADR] Script Date: 01/26/2014 23:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
-- PADR similar to the Oracle function PL/SQL RPAD
Create function [dbo].[PADR] (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
select @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
if @length >= @nLen
set @cString = left(@cString, @nLen)
else
begin
declare @nRightLen smallint
set @nRightLen = @nLen - @length -- Quantity of characters, added on the right
set @cString = @cString + left(replicate(@cPadCharacter, ceiling(@nRightLen/@lengthPadCharacter) + 2), @nRightLen)
end
return (@cString)
end
/****** Object: UserDefinedFunction [dbo].[PADL] Script Date: 01/26/2014 23:30:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Author: Igor Nikiforov, Montreal, EMail: udfs@sympatico.ca
-- PADL(), PADR(), PADC() User-Defined Functions
-- Returns a string from an expression, padded with spaces or characters to a specified length on the left or right sides, or both.
-- PADL similar to the Oracle function PL/SQL LPAD
Create function [dbo].[PADL] (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
select @length = datalength(@cString)/(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
select @lengthPadCharacter = datalength(@cPadCharacter)/(case SQL_VARIANT_PROPERTY(@cPadCharacter,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
if @length >= @nLen
set @cString = left(@cString, @nLen)
else
begin
declare @nLeftLen smallint, @nRightLen smallint
set @nLeftLen = @nLen - @length -- Quantity of characters, added at the left
set @cString = left(replicate(@cPadCharacter, ceiling(@nLeftLen/@lengthPadCharacter) + 2), @nLeftLen)+ @cString
end
return (@cString)
end
