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

Advertisements
This entry was posted in SSIS, Syndication and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s