October 19, 2009 at 3:30 pm
I am trying to submit jobs on the AS400 using SSIS and am trying to execute the following command:
SBMJOB CMD(RUNUBE USER(DEMANTRA) PASSWORD(DEMANTRA) ENVIRON(PD7333) REPORT(
R554100111) VERSION(SNY1001) HOLD(*NO) SAVE(*NO) OWPRINTER('QGPL/DSDPRT'))
JOB(R554100111) JOBD(QGPL/DEMANTRA) JOBQ(QGPL/DEMANTRA)
I can make a connection to the AS400 with no problem to do things like extracting data or inserting records on tables, but I can't figure out how to run this command. Any thoughts would be appreciated. Thanks!!
October 20, 2009 at 12:57 pm
try using a T-SQL task within SSIS.
You will need to setup a Linked Server to your AS400.
DECLARE @VAR1 VARCHAR(100)
SET @VAR1 = 'SBMJOB ....'
EXECUTE (@VAR1) AT [AS400]
October 20, 2009 at 1:33 pm
Thanks for the response. I set up a linked server called 'ODYSSEY' and tried to run this command based on your suggestion below and am getting the following error:
[Execute SQL Task] Error: Executing the query "DECLARE @VAR1 VARCHAR(100) SET @VAR1 = 'SBMJOB CMD(RUNUBE USER(DEMANTRA) PASSWORD(DEMANTRA) ENVIRON(PD7333) REPORT( R554100111) VERSION(SNY1001) HOLD(*NO) SAVE(*NO) OWPRINTER('QGPL/DSDPRT')) JOB(R554100111) JOBD(QGPL/DEMANTRA) JOBQ(QGPL/DEMANTRA) ' EXECUTE (@VAR1) AT [ODYSSEY]" failed with the following error: "SQL0104: Token VARCHAR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . : A syntax error was detected at token VARCHAR. Token VARCHAR is not a valid token. A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token VARCHAR. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Do I need to change the VARCHAR to something valid?
October 20, 2009 at 1:36 pm
My bad .. varchar needs to be at least big enough to hold your string. Try 256 and see if that helps.
October 20, 2009 at 1:53 pm
I am still getting the same error. My connection string is using the IBMDASQL OLEDB connection. Should I use another type of connection parameter?
October 20, 2009 at 2:01 pm
Within SQL Server, under Linked Servers, expand Providers.
Locate IBMDASQL and select Properties.
Make sure "Allow InProcess" is CHECKED.
Also, select Properties of your as400 linked server and verify the following...
Data Access = True
RPC = True
RPC Out = True
Use remote Collation = True
Enable Promotion of Distributed Transactions = True
It took me several attempts to get this to work, and now I am trying to remember the exact setting that solved my problem.
October 20, 2009 at 3:26 pm
I made the changes and it looks like it still does not like the VARCHAR in there. 🙁
October 20, 2009 at 3:43 pm
Let's try something a little simpler to verify the syntax and connection.
Create a CL program that sends a break message to your profile. Make sure the CL runs and produces the message.
Then replace the SBMJOB string in @VAR1 with 'CALL lib.clprog'
Then try your SSIS package and see if that will product the same result.
I have not tried a SBMJOB through this method. I use it mainly to call AS400 programs.
October 21, 2009 at 7:27 pm
I think that calling the CL will probably work out better. I will keep you posted. Thanks for your help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply