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

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)}”

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.