How to search the text bar with wildcard characters in SQL Server

A colleague who applies the Informix database needs to search the text bar with wildcard characters. Although Informix supports wildcard characters in the like and match declarations, this support does not include the text bar. This search is clearly supported by a solution that outputs data to SQL Server.

To complete the search, my colleague had to let Informix output useful data to a text file. A SQL Server DTS package then enters a text file into a local instance of SQL Server and then handles the text bar with a wildcard character.

Unfortunately, there are still two problems: 1 The connection is slow and the connection is interrupted frequently before the input completes. 2 If you decide to cancel the operation halfway through, SQL Server discards all the rows that are already being processed. He had to figure out a way to submit every N records once before continuing.

In order to find a solution for him, I wandered around in the DTS wizard. The discovery solution is built into DTS.

To illustrate how to do this, I create a DTS package that prints the Northwind customers database to a text file. I then created a new package to enter this text file into a copy of the Northwind called Northwind_new. In the DTS wizard, the results are shown in figure A.

Figure A

Now, I'm going to put my mouse over the pipe, right-click and select Properties, and a dialog box named "Transform Data Task Properties" appears. The label you want to use is an option (options), as shown in Figure B.

Figure B

The option to use is always submit last batch and insert batch size. The first option does not need to be described, and the latter option controls the number of rows inserted into the batch. You can adjust it to suit your requirements and application situations. The default setting is 0, which is what my colleague experienced, and if a failure occurs, the entire batch is lost. Setup 1 forces the commit to be committed after each insertion. Set 100 or 1000 to submit once per 100 or 1000 rows.

Now that my colleague's problem is solved, he is happy to search the text bar with wildcard characters.