Bulk Insert From a Text File

I need to bulk insert a long, ugly CSV text file delimited by pipes.

This is how I did it:

BULK INSERT
[TestServer].[StagingSchema].[StagingTable]
FROM 'C:\WeeklyRate.csv'
WITH
( FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

FIRSTROW simply tells SQL that the first row in the file is a header row and to start the import on the next row.

MAXERRORS is the number of errors I'm willing to allow before the process fails.

The FIELDTERMINATOR is whatever character the file uses to seperate fields. In my case, a pipe. If it were a tab I would put, '\t'.

The ROWTERMINATOR tells SQL where the end of the row is. It's usually a carriage return, '\n'. But you might find you have a trailing character at the end of the row. In that case, just stack the value to include the left over character. If I had a trailing pipe, my ROWTERMINATOR statement would be: FIELDTERMINATOR = '\|\n'

Derived Column Expression

I'm building an SSIS package and I want to create a derived column with hard coded value. The column is a bit type and I want to set the default value as "0".

In SSIS, I click the Derived Column component, and right click > Edit

Name the colum somthing.
Select 'add as new column>' for the second option
The expression is (DT_l1)"0"

The type will fill automatically, or you might need to set it yourself. You can leave everything else blank.

Truncate Table and Verify Truncate

SELECT COUNT(*) AS BeforeTruncateCount
FROM DBO.MyTable

TRUNCATE TABLE DBO.MyTable

SELECT COUNT(*) AS AfterTruncateCount
FROM DBO.MyTable

Alter data type on a column

Since people keep asking me, here's how to force a change of datatype on a column even though SQL won't let you do this in Designer:

ALTER TABLE dbo.MyTable
ALTER COLUMN MyColulmn nvarchar(255)null

works on 2005 and 2008

Add a New Column on an Existing Table as a Foreign Key

In this script example, I needed to add a foreign key column onto an existing table. The table did not contain data


ALTER TABLE [FirstTable]
ADD NewKey_ID uniqueidentifier not null,
CONSTRAINT fk_Campaign_ID FOREIGN KEY (NewKey_ID )
REFERENCES SecondTable (NewKey_ID )

Set an Existing Column as the Primary Key

The column that becomes a primary key must be a uniqueidentity column and must not allow nulls.

Here's the script:

ALTER TABLE [MyTable]
ADD CONSTRAINT pk_MyTable PRIMARY KEY (MyColumn_ID)

Configure Sharepoint Report Viewer – My Report or Chart Won’t Display or the Report Server Root Directory Is Displayed

My chart was fine and in the correct place but when viewed in inside a Sharepoint Report Viewer part, I either got an error or the root directory of the Report Server was shown. After doing some research on the web, I discovered this is a common problem. Everyplace I looked told me the same thing…you need a fully qualified path to the Report Server. Nobody mentioned the need to included the instance.

You need to fill in these two boxes in the Sharepoint web part:



You must include the name of your Report Server instance if your Report Server is not installed in the default instance. In my case, Report Server was installed in an instance called Development.

Report Manager URL = http://myserver/report$instance

Report Path = /folder/rdlname