Importing Flat File data into SQL Server with SSIS

Posted on February 24, 2013 by Joshua

Ok, so one of the problems that we come across in the development field is the fact that data is not always consumable in formats that we would wish to get, it may come in tab delimited, pipe delimited, in CSV files, whatever – the main thing here is we need to automate a process that imports the data into your database that has as few moving parts as possible (so when not if you have to dig back in the code you are not left wondering how the hell you built the thing in the first place.)

Turns out it is very doable but a long drawn out process in SQL Server to build a .dtsx package that accomplishes the entire task! I have detailed the steps below as I just know that this will come back and be of use later.

Creating a Job in SQL Server to Import Flat Files

What You Need:

Microsoft SQL Server Management Studio

MSFT SQL server

Overview:

When data comes in from outside sources in flat files that are delimited you need a way to open the files, import the data into the database, and archive the files just in case you have a need to rerun the job, or to take a look at the raw data files in the future. The script will archive the files that have already been processed in folders bearing the month and the year that corresponds with the date the job ran that kicks off the .dtsx package.

Tables:

processed_files – This table will hold the files that have been processed so that 1) you have a record of what has been done, and 2) if the job fails you at least know where it failed.
data_table – This is the table that you are importing data into and will change depending on the files that you are importing.

Steps:

Set up Variables
Set up data connections
Displaying and using the toolbox
Add Foreach Loop Container
Add Execute SQL Task (Check Processed)
Add data Flow Task
- Add Flat File Source
- Add Multicast
- Add OLE DB Destination
- Add Row Count
- Insert Constraint between Check processed and the Import file Data Flow Task
Add Execute SQL Task (Register Processing)
Add Script Task
Add File System Task (Move Files)
Add Execute SQL Task (Update processed table)

Create processed files table:

CREATE TABLE [dbo].[file_control]

(

[ImportedFilename] [varchar](100)   NOT NULL,

[RecordsImported]  [int]            NOT NULL,

[ProcessDate]      [datetime]       NOT NULL

)

Set up Variables:

To display the variables window in Microsoft SQL Server Management Studio hover over the ‘View’ menu item then scroll down to the ‘Other Windows’ option, the resulting fly-out menu should display the variables display option.

Figure 1 – View Menu Bar

The table below lists all of the variables needed to set up the file import solution on the server. These variables will be manipulated later by scripts as well as used as output and input parameters in several of the steps we are about to perform.

Name	Scope	Data Type	Value	Example
Destination Folder	DTS Package Level	String	Archive folder. However, you will need to insert the fully qualified name to the source folder or you will get the error that is visible in Figure 2 – FilePath error is variable is left empty.	Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.
Destination Root	DTS Package Level	String	Path to the root of the archive folder.	Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.
FilePath	DTS Package Level	String	The path and name of the current file in the Foreach loop container. You will need to insert the fully qualified name to the source folder or you will get the error that is visible in Figure 2 – FilePath error is variable is left empty.	Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.
FilePattern	DTS Package Level	String	The extension or name that you want to use as a filter when determining which files the Foreach loop container will process.	.txt, or SALES.txt
MonthNameFormat	DTS Package Level	String	The Visual basic or C# format specifier for the month part of the date. More information can be found in the script section of this document.	MMMM or MMM
Processed	DTS Package Level	Int32	Will hold a flag that will be set to true if the file has already been processed.	0
Records	DTS Package Level	Int32	Will hold the record count, or the number of rows that are present on the file.	0
SourceFolder	DTS Package Level	String	The source folder where the files that you will be processing reside. will be	Fully qualified path. End with a forward slash (\). If you are developing this on a network do not use mapped drive letters use the \\ format.

Figure 2 – FilePath error is variable is left empty

A word about scope:

Scope is very important in the processing of the files, you may be tempted to go with the thought that a variable.

Set up data connections

Figure 3 – Connection Managers tab with context menu.

This .dtsx package will need three connections to add a data connection you will need to right click on the blank area in the ‘Connection Managers’ tab. You will need three connections:

New Flat File Connection
New File Connection
New OLE DB Connection

New Flat File Connection

The connection to the file is where you will have to set up the column mappings in order to get the data from the file to the table in the database. In this example give the connection the name ‘TextFile_Connection’, the rows are separated by a carriage return {CR}{LF}, the format is delimited, and there is one header row that we have to skip before we get to the actual data. See Figure 4 – Flat File Connection General Tab for a view of these settings.

The ‘Columns’ tab is a bit counterintuitive as it is not where we define the actual columns, it is where we set the column delimiter and the row delimiter for the rows that follow the header. In this instance we are dealing with a tab delimited file and each record is separated from the next by a carriage return. See Figure 5 – Flat File Connection Columns Tab for a view of these settings. You may be tempted to skip the next step, especially if the preview pane shows actual data from your input file; if you do you will miss one of the most important steps. Conversely, if you do not see any data in the preview grid after you navigate to the file you want to import then don’t worry, this is actually pretty common, you can add the columns manually in the next tab.

The ‘Advanced’ tab, allows you to change the name of the columns as well as change the data type of the columns. If you are dealing with a data file that does not include column headers, or for some other reason you are unable to get the flat file wizard to recognize the file you have selected you can press the ‘new’ button on the bottom of the screen to add columns to the connection manager. Make sure you rename these columns and match the data type and length to the destination database table. The Advanced tab is displayed in Figure 7 – Flat File Connection Advanced Tab.

Figure 4 – Flat File Connection General

Figure 5 – Flat File Connection Columns Tab

Figure 6 – A test tab delimited text file with one header row

Figure 7 – Flat File Connection Advanced Tab

New File Connection (A connection that points to the Processed Folder)

Figure 8 – New File Connection dialog box

To set up a connection to the folder that will contain all of the archived files (in their dated folders) you will need to add a file connection. This is pretty straight forward, just change the usage type to ‘Existing Folder’ and browse to the folder on the server. Note that if you are using mapped drives you will need to type the location in the Folder textbox using the server name on this format \\servername\somefolder\destfolder. The folder you are pointing to must exist before you complete this step. The File Connection Manager Editor can be seen in Figure 8 – New File Connection dialog box.

New OLE DB Connection

Figure 9 – New OLEDB Connection manager window

Displaying and using the toolbox

Figure 10 – Show / Hide the Toolbox

The toolbox provides drag and drop elements that can be added to the control flow and data flow tabs in SQL Servers Management Studio. It is important to note that the options available within the toolbox change depending on the tab that you are working in. This distinction is usually ignored in many of the online tutorials that you will find, which in leaves the reader wondering why they do not see those options.

To display the Toolbox pane click on the view menu item then press the toolbox icon, or just use the shortcut keys Ctrl+Alt+X.

Figure 11 – Toolbox when Control Flow is displayed

Figure 12 – Toolbox when Data Flow is shown.

The figures above illustrate the different toolboxes. The next section will focus on the items necessary to complete the data import task.

Add Foreach Loop Container

Figure 13 – The Foreach loop Container

The Foreach loop container allows you to loop through a collection of objects. To configure the container double click on it to display the Foreach loop editor which is shown in Figure 14 – Foreach Loop Editor General Tab.

In the General tab change the Name to ‘Loop thru files’.

The next tab is the Collection tab; this is where you will be able to let the container know which directory it is supposed to loop through, as well as which files it should process. In this tab you will have to enter a value in the Folder textbox in the Enumerator configuration section of the application. The Files textbox works just like a file search in windows explorer, so if you wanted to loop through all the text files in a folder you would have to add ‘*.txt’.

The third tab is the ‘Variable Mappings’ tab, and is where we will connect the variable that is specified in the ‘Collection’ tab under the Enumerator configuration section. The variable in this instance will be set to the fully qualified file name.

Finally, in the expressions tab we will set up the variables that will hold the Directory and FileSpec values, this way we can go in later and change them by simply updating the variable’s value. To map the variables just click on the ellipsis in the right hand column of the expressions line of the Foreach Loop editor. Once the Property Expression Editor is displayed you can select a value in the right hand box that you would like to assign the variables value to. Figure 17 – The Expressions tab of the Foreach Loop Editor shows both the Expressions tab and the Property Expression Editor.

Figure 14 – Foreach Loop Editor General Tab

Figure 15 – The Collection tab of the Foreach Loop Editor

Figure 16 – The Variable Mappings tab of the Foreach Loop Editor

Figure 17 – The Expressions tab of the Foreach Loop Editor

Add Execute SQL Task

When looping through the files the application will expect that the incoming data conform to the specifications that are set later in the data flow task. If for some reason the data does not conform to the specifications it will cause the entire job to stop executing. In order to determine where the job stopped running and to provide a means of restarting the job without deleting the data that was successfully input before the job failed it is important to log the file that has been processed and check that this file is not imported twice. This task will check the file log and determine if the file has been run already.

First drop the Execute SQL Task Control Flow Item into the Foreach loop container. Next double click on the container to view the Execute SQL Task Editor, shown in the figure below.

Figure 18 The General tab of the Execute SQL Task Editor

In the tab above change the name to read ‘Check Processed’. Change the result set from None to Single Row, then drop down the connection and if you have not added a connection to the database yet click on <New Connection> and add a connection to the database. Given the table structure for the processed files table found on page one place the following SQL in the SQL Statement area.

SELECT COUNT(*) AS Processed FROM file_control WHERE ImportedFilename = ?

Notice the question mark in the sql statement, this will receive the value in the parameter mapping tab that we will set up now. In the ‘Variable Name’ column drop down the box and select the FilePath variable that was set up earlier. Note that all variables that are user created will be prefixed with the word User and two colons (::). The directions should be Input, the Data Type is VARCHAR, and of course, this is the 0^th parameter so place 0 in the Parameter Name column. Adjust Parameter Size according to your file path size; here 200 will cover anything that is thrown at it.

Figure 19 – The Parameter Mapping tab of the Execute SQL Task Editon

Next click on the ‘Result Set’ tab and in the Result Name column type in ‘Processed’, and in the variable column drop down the box and select the user defined variable named User::Processed.

Figure 20 – The Result Set tab of the Execute SQL Task Editor

Add data Flow Task

Figure 21 – Data Flow Task with Context Menu visible as well as the data Flow tab visible

Next we will add the data flow task by dragging it inside the Loop thru files container. First, drag the green arrow that comes from the Check processed task that you just created to the new data Flow Task and connect the two.

Double clicking on the data Flow Task will take you to the data Flow tab pictured in the image above, which will allow you to drag and drop additional elements inside of the data flow task. The elements that are available will differ when inside the data flow tab. To rename the Data Flow Task you will have to right click on it and select Rename, at this point give the task the name ‘Import file’.

Next , double click the Import file task to change your view to the data Flow tab. Once inside the data flow tab we will need to drag four elements from the toolbox to the tab.

Flat File Source element
Multicast element
OLE DB Destination element
Row Count element

After adding these elements connect them by dragging the green lines as shown in the figure below.

Figure 22 – Arraignment of elements in the Import file task

Add Flat File Source

Figure 23 – The Connection Manager tab of Flat File Source Editor

Double click on the Flat File source element to display the editor window. In the Connection Manager tab you should have the option of selecting the connection manager that points to the flat file you would like to import. You can refer to the file in Figure 6 – A test tab delimited text file with one header row for the file that we are using in this case. Once you have selected the connection move on to the ‘Columns’ section of the editor (pictured in the figure below). The Columns tab will allow you to rename the output from the file into names that actually mean something to you. Here I would suggest using the actual names in the destination table as it will make it easier to map the columns when you get to the OLE DB Destination part of this task. To change the names just click in the Output Column cell and type the name that you would like to be sent to the Multicast element. Once you complete these steps the small red X in the top right hand corner of the element should disappear. You can think of this X as an error in the compiler if you were writing code.

Figure 24 – The Columns tab of Flat File Source Editor

Add Multicast

The Multicast element is one of the few that does not need any changes once placed. This element will simply take the input from the Flat File Source and route it to two locations. In this instance those locations are the DB destination, to actually insert the data into the database, and the row count element to determine how many rows were inserted for logging purposes.

Add OLE DB Destination

The OLE DB Destination will point to the table that will receive the data from the file. The SQL to create the test table that corresponds with the test data file in Figure 6 – A test tab delimited text file with one header row is below:

CREATE TABLE [dbo].[file_import_test](

[TestColumn1] [int] NULL,

[TestColumn2] [varchar](200) NULL,

[TestColumn3] [float] NULL,

[TestColumn4] [float] NULL

)

First, double click on the DB connection to display the OLE DB Destination Editor (pictured in the image below). Next, drop down the connection manager and select the connection that points to your database, which should have been set up earlier. You will want to select ‘table or View’ for the data Access Mode, and finally select the actual table where you want the file data to be routed to in the name of the table or the view dropdown box. Finally, we will map the columns from the file to the table columns. Click on the Mappings tab in the left window pane.

Figure 25 – The Connection Manager tab of the OLE DB Destination Editor

If you configured your flat file column names to match the table column names then this part will be pretty easy, just verify that they link up. If not you are left with the task of connecting the file column outputs with the table columns and then connecting them by dragging and dropping the lines between them to the correct columns. Once you complete this task the small red X in the OLE DB Destination element should disappear.

Figure 26 – The Mappings tab of the OLE DB Destination Editor

Add Row Count Element

The row count element will count the rows that have been read from the file and store them in the variable that has been designated in the custom properties section of the Component properties tab pictured below. All that needs to be done here is the variable User::Records should be selected from the drop down box in the column next to variable name.

Figure 27 – The Advanced Editor for Row Count

Insert Constraint between Check processed and the Import file Data Flow Task

The final step in the addition of the Import file data flow task is to set up a constraint between the check processed SQL task and the data flow itself. The constraint will make sure that duplicate records do not get inserted into the table. To accomplish this double click the green arrow that connects the two elements to display the Precedence Constraint Editor.

Figure 28 – Precedence Constraint Editor

In the window pictured above change the drop down box from the default setting of Constraint to Expression and Constraint. Once this has been completed the Expression text box will open where you enter @Processed == 0, which corresponds to the variable being passed by the Check processed SQL task. Leave the default setting in Multiple Constrains where it is and press OK.

Add Execute SQL Task (Register Processing)

Next , drag another Execute SQL task element to the Foreach Loop Container and drop it inside the container directly under the data flow task. Right click on the task and rename it ‘Register Processing’.

Figure 29 – The General tab of the Execute SQL Task Editor

In the General tab of the Execute SQL task Editor select the database connection from the connection dropdown box. In the SQL Statement text area insert the following SQL:

INSERT INTO file_control (ImportedFilename, RecordsImported) VALUES (?, ?)

Figure 30 – The Parameter Mapping tab of the Execute SQL Task Editor

Next click on the parameter mapping tab and add the following parameters to the window on the right hand side, 1) User::FilePath with a direction of Input and a datatype of VARCHAR and a size of 200, and 2) User::Records with adirection of Input and a type of LONG and a size of -1. The parameter name of the first variable is 0 and the parameter name of the second variable is 1.

Add Script Task

Next drag the Script Task element from the toolbox and drop it directly under the Register Processing SQL task. Right click on the element and give it the name Set Destination. Double click on the element to display the Script Task Editor window. Make sure the Script language is set to Microsoft Visual Basic 2008 then press the Edit Script … button at the bottom of the window to bring up the code editor. The script that will create archive directories is below and will need to replace the code that is between the following lines:

Public Sub Main()

…..

End Sub

<b>Microsoft Visual Basic 2008 Code</b>

Dim varCollection As Variables = Nothing

Dts.VariableDispenser.LockForRead("User::FilePath")

Dts.VariableDispenser.LockForRead("User::DestinationRoot")

Dts.VariableDispenser.LockForRead("User::MonthStartPosition")

Dts.VariableDispenser.LockForRead("User::MonthNameFormat")

Dts.VariableDispenser.LockForWrite("User::DestinationFolder")

Dts.VariableDispenser.GetVariables(varCollection)

Dim SourceFilePath As String = varCollection("User::FilePath").Value.ToString()

Dim FileName As String = SourceFilePath.Substring(SourceFilePath.LastIndexOf("\") + 1)

Dim DestinationRoot As String = varCollection("User::DestinationRoot").Value.ToString()

Dim MonthStartPosition As Integer = Convert.ToInt32(varCollection("User::MonthStartPosition").Value)

Dim MonthValue As Integer = 0

Dim Yearvalue As Integer

Dim MonthNameFormat As String = varCollection("User::MonthNameFormat").Value.ToString()

Dim FolderName As String = String.Empty

Dim MonthwiseDirectory As String = String.Empty

MonthValue = Month(Now())

Yearvalue = Year(Now())

If FileName.Length > 0 AndAlso MonthValue > 0 Then

FolderName = New DateTime(1, MonthValue, 1).ToString(MonthNameFormat) & " " & Yearvalue

End If

MonthwiseDirectory = System.IO.Path.Combine(DestinationRoot, FolderName)

If Not System.IO.Directory.Exists(MonthwiseDirectory) Then

System.IO.Directory.CreateDirectory(MonthwiseDirectory)

End If

varCollection("User::DestinationFolder").Value = MonthwiseDirectory

Dts.TaskResult = ScriptResults.Success

The code above will create a folder structure for archiving the files that have been processed using a month / year naming convention as shown in Figure 31 – Example of the Archived File Structure.

Figure 31 – Example of the Archived File Structure

The parent folder for these monthly folders is determined by the value in the variable DestinationFolder.

Add File System Task (Move Files)

Figure 32 – File System Task Editor General Settings Tab

Next drag a File System Task element from the toolbox and drop it inside the Foreach Loop Container under the Script Task then connect the greed arrow from the Script Task to the File System Task. Right click on the element and rename it Move Files. This will be the element that physically moves the files to the archive location.

To move the processed files change the following settings in this order, or it will not work:

Destination Connection Section

IsDestinationPathVariable = true, this in turn changes the text in the box below it from Destination Connection to DestinationVariable
DestinationVariable = User::DestinationFolder
OverwriteDestination =false

Source Connection Section

IsSourcePathVariable = true, this in turn changes the text in the box below it from SourceConnection to SourceVariable
Sourcevariable = User::FilePath

Add Execute SQL Task (Update processed table)

The last task that you have to complete before setting up the job that will kick off this entire process is to drag an Execute SQL Task element to the Foreach Loop Container and drop the element directly below the Move Files element that you just completed. Right click on the object and rename it ‘Update processed table’, as the name suggests this task will actually go out and rename the files appending the word processed to the imported file name so that the application will not skip the file when it is dropped in the new file location tomorrow (this assumes that you are working on files that will be coming in using the same name every day which is very common!). You should be getting pretty good at the SQL Task object by now so here are the steps required to update the file names.

Figure 33 – Your completed .dtsx package should look like this

Visual Basic and C# date Time Format Specifiers:

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

What’s the difference between UNION and UNION ALL

Posted on February 15, 2012 by Joshua

I recently ran into a situation where I had to provide a summary of items ordered in a table and needed to union the products table in order to get all of the lines that also contained a zero as I had a requirement to show a summary of all the quantities ordered as well as line for each product on the products table that did not have any orders in the orders table yet.

Let’s try to solve this using the tables below, and in turn learn a little about the differences between UNION and UNION ALL:

Products

id	product	description
1	ajax	clean anything
2	glo-brite	mopping solution
3	steel wool	scrub anything
4	soap	plain and good

Orders

fk_product_id	total
1	200
2	300
1	200
1	50

Query

The query below

Select orders.total AS ordered,products.product
FROM orders INNER JOIN products ON orders.fk_product_id = products.ID
UNION
SELECT        '0' AS ordered, product
FROM            products AS products_1

Returns

ordered	product
0	ajax
0	glo-brite
0	soap
0	steel wool
200	ajax
300	glo-brite
50	ajax

Notice that all of the orders for ajax did not get returned in the query. Since we used union it discarded the duplicates in this instance. Now let’s see what happens when we replace UNION with UNION ALL in the query.

ordered	product
200	ajax
300	glo-brite
200	ajax
50	ajax
0	ajax
0	glo-brite
0	steel wool
0	soap

When using all we tell the query to get every record from both queries without removing the duplicates as you can see in the results displayed above. This is important because if we were to finish the query to the specifications in the requirement in the first paragraph we would expand the query to get the totals using the SQL below, which would result in a missing order count of 200 for ajax when using UNION inside our inner query.

SELECT SUM(ordered) as orders, product
FROM(
Select orders.total AS ordered,products.product
FROM orders INNER JOIN products ON orders.fk_product_id = products.ID
UNION ALL
SELECT        '0' AS ordered, product
FROM            products AS products_1) as source_table
Group by product

orders	product
450	ajax
300	glo-brite
0	soap
0	steel wool

Creating Full Text Search Indexes and Queries in SQL Server 2008

Posted on July 7, 2011 by Joshua

Recently I had the task of comparing data across several varchar columns in one table to one large varchar column in another table. The first solution that came to mind was to split the words in the columns in the first table into an array and loop through this array using the ‘LIKE’ statement to determine if the words existed in the other tables column. I would then count the number of hits and like magic I would have a rudimentary search engine. The problems with this method seem obvious, first, the words would have to be surrounded by the wildcard character ‘%’, which matches any number of characters allowing the word to be found anywhere in the sentence, now take the following example. You are running a medical coding application and are pattern matching ‘para’ with this query

Select Desc from icd_10_codes where Desc LIKE '%para%'

You would get results like paralytic, parastrongylus, parasitic, and the list would go on. Give LIKE a try here to see what I mean, http://www.learnsomethings.com/icd9app/index.html The results might not be that useful in this instance. Second, think about the code that you will need to identify all of the punctuation marks and such as ‘,colon’, will not match the word colon as hard as you try if preceded by a percent sign, you can avoid this headache as well as statistically rank the confidence level of each result by using the full text search index in SQL Server 2008. The basic steps involved are,
Create a fulltext catalog on your database, in SQL 2008 this is a virtual object and does not belong to a file group, in SQL 2005 the catalog was a physical structure that lived on the hard drive. In addition, in 2008 the full text search I snow integrated into the database engine, opposed to being based on the MSSearch engine which is actually part of Microsoft Office. In simpler terms, you should have an easier time backing up / reinstalling the catalog and it should not be as prone to corruption. The SQL used to create the catalog is:

CREATE FULLTEXT CATALOG ft_cat  AS DEFAULT;

If you want to take a look at the catalog in the future just run the query below and you can see all of the information pertaining to catalogs in your database.

SELECT fulltext_catalog_id, name, path, is_default, is_accent_sensitivity_on, data_space_id, file_id, principal_id, is_importing
FROM sys.fulltext_catalogs

Next, create the full text index on the tables containing the columns that you would like to index. If you have a table named products with the following structure:

Column Name	Type
Id	Int (Primary Key with an index name of PK_ID)
Desc	Varchar(2000)
Model	Varchar(2000)

You would use the following code to add the Desc and Model fields to the full text index.

CREATE FULLTEXT INDEX ON products
(
  Desc
     Language 1033,
  Model,
     Language 1033
 )
 KEY INDEX PK_ID;

You may have noticed that the language has been set to 1033, which is English, if you need to switch the language you can get a list of the supported languages in SQL 2008 by once again querying the sys tables using the SQL below. The lcid is what you pass with the language clause.

SELECT        lcid, name
FROM            sys.fulltext_languages

Finally, all that is left is to query the database, and this is the step where it becomes apparent that a free text search is way more powerful than the Like clause, and also much faster. The example SQL below will search the product table using the words in the sentence provided and display the primary key, the Desc column and the rank of the result.

SELECT        TOP (20) KEY_TBL.RANK, FT_TBL.Desc, KEY_TBL.[KEY]
FROM            products AS FT_TBL INNER JOIN
                         FREETEXTTABLE(Model, Desc,'This would be a very long sentence that you want to use to search the database ') AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

You may have noticed that the query above actually searches two columns at the same time, and the rank, well, a Like statement will not give you that. Another feature is the ability to add a stop word list that will take common words out of the equation, this will help you pare down the results and deliver an even more accurate search experience for your users. To attach a stop word list to the sample index above just perform the following steps.

CREATE FULLTEXT STOPLIST stList
FROM SYSTEM STOPLIST;

You can query the stop list by running the following query, you may want to limit the results by language or stop list name later.

SELECT        sys.fulltext_stopwords.stoplist_id, sys.fulltext_stopwords.stopword, sys.fulltext_stopwords.language, sys.fulltext_stopwords.language_id,
                         sys.fulltext_stoplists.name
FROM            sys.fulltext_stopwords INNER JOIN
                         sys.fulltext_stoplists ON sys.fulltext_stopwords.stoplist_id = sys.fulltext_stoplists.stoplist_id

You can add words and remove words using the query below.

	ALTER FULLTEXT STOPLIST stList
ADD 'bird' LANGUAGE 1033;

And delete by replacing ADD with DROP in the query above.

More information can be found on the Microsoft site at http://msdn.microsoft.com/en-us/library/ms142571.aspx

Learn Some Things

Things I picked up along the way …

Category Archives: Database Stuff

Importing Flat File data into SQL Server with SSIS

What’s the difference between UNION and UNION ALL

Creating Full Text Search Indexes and Queries in SQL Server 2008