Bulk Insert data to specific columns

Bulk insert will fail if we try to insert data to specific columns in a table, e.g. we have a Candidate table where we store the list of candidates and has an Identity column CandidateID

CREATE TABLE Candidate

(CandidateID INT IDENTITY(1,1) PRIMARY KEY,

 CandidateName VARCHAR(60),

 DOB DATETIME

)

Suppose our source file has only columns "CandidateName" and "DOB",  If we do BULK INSERT from source file to this candidate table as,

BULK INSERT Candidate

FROM 'F:\Candidates.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

We will end up in an error,

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (CandidateID).

An easy method to avoid this error is to create a view against the destination table with columns that we can provide as input and BULK INSERT to the created view.

CREATE VIEW vw_Candidate

(

 CandidateName VARCHAR(60),

 DOB DATETIME

)

BULK INSERT vw_Candidate

FROM 'F:\Candidates.csv'

WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Now, the BULK INSERT is successful and our base table has the data.


Thank!

Adding dynamic connection in SSIS

I have seen many post regarding "Adding dynamic connection in SSIS" in various SQL Sever forums, the question is like

Hi

Whats the best way to edit the connection of a slowly changing dimension. I want to point to a new connection. 

CheersI

 The best way is to use  XML configuration file to change the connection dynamically, using this method saves the SSIS configuration in an XML files, and  later we have the flexibility to edit this xml file Programmatically (from the Application) or manually. The steps are as follows

·         Right click on package designer, choose "Package Configuration and Organizer"

·         Click Add, this will open up a wizard, Click next.

·         In Select configuration type window, choose" XML Configuration file" as your "configuration type". Specify a location to save this configuration file in "Configuration File Name" and click next

·         In "Select Properties to Export Window", expand "Connection Managers", Choose Servername, (initialCatloge, only if you need to edit database as well) in both source and destination and click next button.

·         Specify a name for this configuration and click finish to complete the wizard

Check the XML configuration file created above, you can customize the connection by editing this config file.