issues in executing as400 command from SSIS execute sql task

Question

I am working on an ssis package to process data from text file into as400 system. In one of the steps, i have to create a copy of an existing file object in iseries and i am executing the below command from execute sql task.

{CALL QSYS.QCMDEXC('CRTDUPOBJ OBJ(CKFMTWRK) FROMLIB(MYWRKLIB) OBJTYPE(*FILE) TOLIB(MYWRKLIB) NEWOBJ(CKFMTWRKPN) DATA(*NO)',0000000101.00000)}

This command works fine when i run it from the IBM Personal Communications terminal but throws below error when i do it from SSIS package.

[Execute SQL Task] Error: Executing the query "CALL QSYS.QCMDEXC('CRTDUPOBJ OBJ(CKFMTWRK) FROMLIB..." failed with the following error: "Routine *N in *N not found with specified parameters. SQLSTATE: 42884, SQLCODE: -440". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Further information on my environment:

I'm using sql server data tools 2015. I've established the connection to as400 using Microsoft OLEDB provider for DB2. I have sufficient permissions on the server (i guess, coz i am able to execute the commands from the terminal)

This is my first ever with as400 and I am stuck here in this step. I tried looking up online but there is nothing much that could help me in resolving this.

Any suggestions/help on this would be much appreciated.

Thanks. Dvr

before asking your first question is it better to read the Tour Page to learn more on asking question and other helpful informations, also to get your first informed badge — Hadi
– Hadi, Commented Jun 16, 2017 at 14:06
My documentation shows that command length parameter without the decimal point and padding. Maybe your dev tools are trying to pass the value as a float rather than a decimal value. — jmarkmurphy
– jmarkmurphy, Commented Jun 16, 2017 at 19:24
I tried without the padding and it still doesn't work :( I have no idea how to go about it!! — dvr
– dvr, Commented Jun 18, 2017 at 4:55
Can't tell what OS version you use, but "as400"s are so old they won't run any recent release. If you have newer system, try QSYS2.QCMDEXC instead of QSYS.QCMDEXC, and leave off the length parm. Only pass the command string in. If that doesn't work, please let us know the OS version/release. — user2338816
– user2338816, Commented Jun 19, 2017 at 9:48

Kelvin Chan · Accepted Answer · 2017-06-19 06:48:42Z

0

You are trying to perform AS/400 OS level command from a SQL environment (SSIS) which is not correct. QCMDEXEC is AS/400 command line interpreter while CRTDUPOBJ is creation of an identical object from the source with the option to duplicate its data as well.

Personal Communication terminal is fine cos you think you are using SQL service but actually not. It's AS400 command.

End up, you better do it over AS/400 terminal for duplicating object. Just use

CRTDUPOBJ OBJ(CKFMTWRK) FROMLIB(MYWRKLIB) OBJTYPE(*FILE) TOLIB(MYWRKLIB) NEWOBJ(CKFMTWRKPN) DATA(*NO)

The last keyword DATA(*NO) means create the object only but not the data, so you will get an empty file after completion.

answered Jun 19, 2017 at 6:48

Kelvin Chan

235 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dvr Over a year ago

As suggested in a blog, I created an external procedure as below and called this from my SSIS package.This dint work as well. CREATE PROCEDURE QSYS.QCMDEXC(IN CMDSTR VARCHAR(1024),IN CMDLENGTH DECIMAL(15,5)) EXTERNAL NAME QSYS.QCMDEXC GENERAL And then i thought there could be ambiguity in the naming and tried with below procedure and still failed. CREATE PROCEDURE CLOCTEST.CMDEXC(IN CMDSTR VARCHAR(1024),IN CMDLENGTH DECIMAL(15,5)) EXTERNAL NAME CLOCTEST.CMDEXC LANGUAGE C GENERAL Is there something wrong here too? Does this method work at all? Or should i just give it up?

user2338816 Over a year ago

@dvr Again, you don't tell your OS version, so a good answer isn't possible. However, you're declaring a VARCHAR parm and QCMDEXC accepts only a CHAR parm. I.e., signatures won't match. But if you try from QSYS2 instead of QSYS, you might already have a proc that accepts VARCHAR and doesn't need a length parm... depending on the OS version.

user2338816 Over a year ago

@dvr ...but even on earlier versions, QSYS.QCMDEXC will work as long as you supply a CHAR command parm along with a length. If you can only give a quoted string, it has to be CAST as a CHAR.

Collectives™ on Stack Overflow

issues in executing as400 command from SSIS execute sql task

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related