The SQL Task does not support the notion you have mentioned (e.g. $DATA[1]}), however, it is possible to map columns in a different order than the default (sequential) order. This can be achieved by defining one or more parameters under the query element of the task. By defining parameters, you can map columns of input row set in any order you like as well as map a column in the input rowset to more than one parameter. Parameter mapping also allows you to skip one or more columns of the input rowset.
Below is a sample project to demonstrate how parameter mapping can be done in the SQL task:
Code: Select all<project name="SQL Task Parameter Mapping" mainModule="Main" version="1.0">
<description></description>
<module name="Main">
<readCSV inputFile="C:\Users\spullabhotla\Desktop\persons.csv" outputRowSetVariable="data" />
<sql label="Connect to DB" resourceId="Production">
<query inputRowSetVariable="${data}">
<statement>insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)</statement>
<param index="1" type="INTEGER" mapFrom="1" />
<param index="2" type="CHAR" mapFrom="2" />
<param index="3" type="CHAR" mapFrom="3" />
<param index="4" type="VARCHAR" mapFrom="2" />
<param index="5" type="CHAR" mapFrom="3" />
<param index="6" mapFrom="4" />
<param index="7" mapFrom="5" />
</query>
</sql>
</module>
</project>
The above project reads persons data from a CSV file as shown below:
Code: Select all1001,John,Doe,1 Main St.,Omaha,NE,68135
1002,John,Smith,3820 Dodge St.,Omaha,NE,68125
1003,Cindy,Clark,7000 S 100th PLZ,Omaha,NE,68128
The data from the CSV file is inserted into a database table with the following columns:
Code: Select allField Name Type/Size
PERSON_ID INTEGER
FNAME CHAR(30)
LNAME CHAR(30)
NAME VARCHAR(61)
ADDRESS VARCHAR(255)
ZIP CHAR(10)
The table above has a NAME filed to hold the concatenated value of first name and last name. Also, the table does not have city and state columns.
The insert Query used in the project is -
insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)
The above query populates the name field by concatenating the first name, followed by a white space and last name. Then it also populates the normal first name and last name fields. This is done by defining a custom parameter mapping.
Code: Select all <param index="1" type="INTEGER" mapFrom="1" />
<param index="2" type="CHAR" mapFrom="2" />
<param index="3" type="CHAR" mapFrom="3" />
<param index="4" type="VARCHAR" mapFrom="2" />
<param index="5" type="CHAR" mapFrom="3" />
<param index="6" mapFrom="4" />
<param index="7" mapFrom="5" />
The above parameter mapping hints GoAnywhere to:
- Map 1st parameter (?) in the query from 1st column of the input rowset.
- Map 2nd parameter (?) in the query from 2nd column of the input rowset.
- Map 3rd parameter (?) in the query from 3rd column of the input rowset.
- Map 4th parameter (?) in the query again from 2nd column of the input rowset.
- Map 5th parameter (?) in the query again from 3rd column of the input rowset.
- Map 6th parameter (?) in the query from 4th column of the input rowset.
- Map 7th parameter (?) in the query from 5th column of the input rowset.
As you might have already noticed, we did not use the city and state data that was available in the input CSV file.
