Skip to main content
  • EN

This has been bothering me for a long time. I keep avoiding it because I just end up using a SQL executor. to find errors

How is the Writer transformer different than a SQLExecutor when performing MSSQL inserts? [or other functions]. Don’t they use the same driver?

  • Here is an example: I recently inserted a row into a table where the string length was too long got the field. The Writer transformer just inserted the string successfully, but it truncated.
  • The SQLExecutor threw a SQL error saying, ‘string or binary data would be truncated’ and then failed\rejected the insert.
  • I have also run across other odd behavior in the past. Such as the writer having the ability to insert duplicate foreign keys, but the SQLExecutor will fail with error.
  • Is there a good resource for this or a good explanation? 

Thanks!

Tagging @ctredinnick as he might have some insights (he is away this week but back on the 9th)

The Writer has a bit of a wrapper around it where it will query the database for what’s there, and check that it can write before it tries to, does the table already exist, what’s its schema. SQLExecutor just runs whatever you give it, with no protection. Error handling is a key difference for sure.

The SQLExecutor is more work than using a writer because you have to write the insert statement yourself, it offers a lot more flexibility as well as a lot more opportunities to make mistakes. Like with the SQLExecutor you have to be careful about every datatype because any incompatibility will throw an error, but it gives you the option to use functions like getdate() or suser_sname() etc in your insert, or a try..catch. Or insert to two tables if you need to separate it feature by feature.

The writer if set to bulk insert may be able to insert duplicate foreign keys, because it will disable those checks before writing (foreign key constraint or trigger). 

For < 100 inserts, performance between the two transformers is comparable, but over 1000 inserts the writer is definitely faster, even if you batch the inserts in your own SQL statement.

I’m not aware of a good resource describing the pros/cons of the two

@ctredinnick

Thanks for the input!

I wish there was a link or something with more in-depth information on the transformations. The ‘normal’ links cover basic function and offer some decent examples but lack in more detailed design and function.

Reply


Community Stats

30,351
Posts
115,342
Replies
38,082
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.