Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Importing data from an AS400 database in SSIS Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 11:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 2:01 PM
Points: 14, Visits: 88
So I am attempting to import data from an IBM AS400 database into SQL Server. I can do it easily enough via linked server - however the processing time is off the chart. In an attempt to speed things up i am trying to create an SSIS package that will import the data. The problem I am having is that the date on the AS400 is stored in the format CYYMMDD. So I need to pull 90 days back when I import the data into SQL. Does anyone have any experience in converting a SQL date to CYYMMDD in SSIS?

I have 2 functions in SQL that will convert it to the appropriate format:

The Main Fuction is below:

CREATE FUNCTION [dbo].[fnStdToDate]

(@SQL_Date DATETIME)

RETURNS INT

AS
BEGIN

RETURN CAST(CASE WHEN YEAR(@SQL_Date) > 1999 THEN '1' ELSE '' END +
SUBSTRING(CAST(YEAR(@SQL_Date) AS VARCHAR(4)),3,2) +
dbo.udf_padl(CAST(MONTH(@SQL_Date) AS VARCHAR(2)),2,'0') +
dbo.udf_padl(CAST(DAY(@SQL_Date) AS VARCHAR(2)),2,'0') AS INT)

END



CREATE function [dbo].[udf_padl]
(
@cString nvarchar(4000)
, @nLen smallint
, @cPadCharacter nvarchar(4000) = ' '
)
returns nvarchar(4000)
as
begin
declare @length smallint
, @lengthPadCharacter smallint

if @cPadCharacter is NULL or datalength(@cPadCharacter) = 0
set @cPadCharacter = space(1)
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
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

CREATE function [dbo].[udf_padr] (@cString nvarchar(4000), @nLen smallint, @cPadCharacter nvarchar(4000) = ' ' )
returns nvarchar(4000)
as
begin
declare @length smallint, @lengthPadCharacter smallint
if @cPadCharacter is NULL or datalength(@cPadCharacter) = 0
set @cPadCharacter = space(1)
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


So i can run the following sql and get the converted date:

select dbo.fnStdToRdcDate(getdate()-90)

Result: 1130517

So my question is how can I make this compatible with SSIS? It seems I am somewhat limited with the expression builder. Thanks in advance for any help and comments.
Post #1484853
Posted Thursday, August 15, 2013 4:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:33 AM
Points: 321, Visits: 577
Can't you convert to the date to a string and then use expression builder to pull out the appropriate sections of the string into the format you require?

This 'should' return today - 90 days in CCYYMMDD:

(DT_STR,4,1252) DatePart("yyyy",getdate()-90) + (DT_STR,4,1252) DatePart("MM",getdate()-90) + (DT_STR,4,1252) DatePart("DD",getdate()-90)


SQL SERVER Central Forum Etiquette
Post #1484942
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse