Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

AS400 Program from SSIS

If you need to call an AS 400 program from an SSIS package this can easily be accomplished with an Execute SQL Task. This can be made even easier with two variables and an expression too. The AS400 program QCMDEXC is usually called using the CALL QSYS.QCMDEXC command. This is then followed by a command like CLRPFM FILE(MDAVIS/APPLSQL). Then this is followed by a ten digit string of numbers that contain the number of characters in the command. Then a period followed by five more zeros.

So the complete above command would be:

{CALL QSYS.QCMDEXC (‘CLRPFM FILE(MDAVIS/APPLSQL)’, 0000000026.00000)}

This can be typed into the Execute SQL Command or typed into a variable. But wouldn’t it be nice if the numbers after the command would automatically generate? With a small expression we can make that happen.

Here are the two variables I created on my SSIS package:

strAS400CMD – String variable for the command

strAS400FullCMD – String variable evaluated as an expression to complete the command

clip_image002

The variable strAS400CMD holds the value of “CLRPFM FILE(MDAVIS/APPLSQL)”.

The variable strAS400FullCMD holds the expression

“{CALL QSYS.QCMDEXC (‘” + @[User::strAS400CMD] + “‘, “+

Right(“0000000000″ + (DT_WSTR, 10) (Len( @[User::strAS400CMD])) ,10)+”.00000)}”

clip_image004

This expression will automatically calculate the length of the string for the command variable and create the number string needed afterwards. In the Execute SQL task set the SQL Source Type to variable and select the strAS400FullCMD variable.

clip_image006


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...