5

I want to use "Table exists" step to check if certain table exists if not then create one

The transformation I have created (in order to copy data from input database into output database)

Table Input ----->  Table exists  ----> Table output

The properties in "Table exists" are 

Step name:
connection:
Schema name:

Tablename field:
Result fieldname:

The first three are clear. The last two

What does "Tablename field" mean ? It is a drop list with limited options which are column names of input table. Should it be the table name I want to check in connection ?

What is Result fieldname ? How can I use that ?

Hello lad
  • 17,344
  • 46
  • 127
  • 200

2 Answers2

6
  • Tablename field is the table it checks for.
  • Result fieldname The name of the field that will contain the boolean result flag in the output of this step.

Use the following sequence to check and create table if not exist.

enter image description here

Table Input - Get the data in, from the database.

Generate rows - Specify tablenamefield as name and table_name_to_check as value and String as type. Later we can use the field tablenamefield in Table exists step

Table Exist - Select tablenamefield which appears in the Tablename field drop down. Write any name as the Result fieldname i.e result

Switch/ Case - Type Y in case values and Table Output as the target step. Make execute SQL statement as Default target step. field name to switch is result

Execute SQL statement - Write your table creation SQL query.

Table Output - Writes to the new table.

NB: If you don't want to check whether the table exist for each row. you can use a job and check for the table existence in first transformation and Table input and output steps in second transformation which I recommend for performance.

Marlon Abeykoon
  • 11,927
  • 4
  • 54
  • 75
  • thanks for the response. In my spoon, Table input can not hop to Generate Rows – Hello lad Nov 22 '15 at 19:41
  • Any error? If not you can generate a constant field in your query in Table input like SELECT field1, field2, 'tablename' as tablenamefield from table. So that you dont need to use Generate Rows step – Marlon Abeykoon Nov 22 '15 at 19:44
0

the Tablename field is the field that contains the name of the table(s) you want to check. If you only want to check a single table, you only need a single row with the table name in one of the fields.

The Result fieldname is the name of a new field the step is going to add to the step output. That new field contains the result of the check. It is a boolean field you can then use in later steps, for instance to send this row into a branch that creates the table. The field value is true if the table exists and false if the table doesn't exist.

Dirk Trilsbeek
  • 5,873
  • 2
  • 25
  • 23
  • thanks for the response. I am not allowed to fill in the name of table I want to check, it is a drop list with limited options which are column names of the table I want to check. Is that possible that I have connected the steps in a wrong way ? – Hello lad Nov 22 '15 at 16:15
  • no. This is how ETL steps usually work - they take their parameters from fields. So your "check table"-step is executed for every single row in your transformation and it receives the table name from a row in its input. You can't manually enter a field name in the step. – Dirk Trilsbeek Nov 22 '15 at 16:52