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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy