I set up a sql express database to import data from our production server (AS400). I don’t want to constantly be pulling in entire tables of data to update my reporting database, so I created a script to handle the initial load, which then logs a load date and time (in separate columns, due to the way AS400 captures update date and time). I then want to use these as variables in an automation script to go get the updated records. So, I created 2 automation variables using sql queries selecting the logged date and time from the sql express database. Within the update query to be run against the AS400, I added two statements in the WHERE clause using the variables. However, when I try to add the import wizard, I cannot get past the Add Query section, because I get an error stating that the number of parameters is incorrect. I am able to do the same thing but instead exporting the query results to a CSV if I only use 1 variable - I actually get a different error here if I use 2.
Is there any way to use 2 bind variables and an import wizard as part of an automation script without getting the error? I can try to recreate and post the specific message.
Let’s forget about importing through Automation for a second. Do I understand correctly that you were able to create import template that uses bind variables, and successfully import data using this template providing bind vars value during execution?
Hi Igor - I am not able to run an import at all using bind variables when the data source points to the AS400 source. I get an error that says “Wrong number of parameters”
Also, if I put a select to file activity in the script, it only allows me to use 1 variable. If I try to include a second it says "File_1 - The handle is invalid. (Exception from HRESULT: 0x80070006 (E_HANDLE)) "
When I change the result query to something on the sql express database (still a select to query), it works fine. Interestingly enough, I got a similar message for sql express when trying the import feature, but when I declared the variables first, it let me continue with the import. However, there is another bug in the import that Debby is aware of that prevents using multiple batches in the import script, which I would need to do in order to declare the variables in AS400 sql. This is becoming kinda complicated.
I can try to take step by step snap shots of what I am doing tomorrow. I am tired to dealing with this now. Thanks for your help!
Igor, I sent you a bunch of information, including my work-around. I’m using 3.4. I’ll post my complete workaround after you have had a chance to review my findings.