August 15, 2013 at 11:15 am
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.
August 15, 2013 at 4:44 pm
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)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply