SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Importing data from an AS400 database in SSIS


Importing data from an AS400 database in SSIS

Author
Message
smrobin
smrobin
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 136
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.
Jim Mackenzie
Jim Mackenzie
SSC Eights!
SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)SSC Eights! (849 reputation)

Group: General Forum Members
Points: 849 Visits: 726
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search