Forcing Results To Conform For Exports

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