SSIS – Using BULK INSERT Part -II

Introduction

This article is the continuous of my previous article named SSIS – Using BULK INSERT. If you not read it earlier you can move on the link.

In this article we are just using the Format File and BULK INSERT command with SSIS component. Hope it will be informative.

What the Scenario is

We have a Flat file and we want to copy the flat file to a MS SQL Server table by using BULK INSERT command. We also use the Format file in XML or Text file format.

How We Do that

 Step – 1 [ The Destination Table Objects ]

--- Destination Table Objects ---

IF OBJECT_ID(N'[dbo].[tbl_StudentDetails]', N'U')IS NOT NULL

   DROP TABLE [dbo].[tbl_StudentDetails];

GO

CREATE TABLE [dbo].[tbl_StudentDetails]

    (

        StdRoll   INT         NOT NULL PRIMARY KEY,

        Stdname   VARCHAR(50) NULL,

        StdClass  INT         NULL,

        Section   CHAR(1)     NULL

      )

GO

Step – 2  [ The Control Flow of Package ]

Step – 3 [ The Variables ]

Name	Type	Default Value
vFormatFile	String	E:\Preparation\SSIS\BCP\FmtStudentDetails.xml
vSQLInstance	String	JOYDEEP-LAPTOP\JOYDEEPSQL12
vTextFile	String	E:\Preparation\SSIS\BCP\test.txt

Step – 4 [ The Execute SQL Task Named Truncate Destination ]

This is nothing but TRUNCATING the Destination for Full Load Purpose.

TRUNCATE TABLE [dbo].[tbl_StudentDetails];

Step – 5 [ Execute Process Task named Create Format File ]

This process task is used for creating the format file of Table.

The Expression is

" PracticeDB.dbo.tbl_StudentDetails  format nul -c -f " + @[User::vFormatFile] +" -x -S "+ @[User::vSQLInstance]  +" -T -q -t;"

Evaluated Value

PracticeDB.dbo.tbl_StudentDetails  format nul -c -f E:\Preparation\SSIS\BCP\FmtStudentDetails.xml -x -S JOYDEEP-LAPTOP\JOYDEEPSQL12 -T -q -t;

Step – 6 [ Execute Process Task named Execute BCP ]

The Expression is

" PracticeDB.dbo.tbl_StudentDetails IN "+ @[User::vTextFile]  +"  -f "+ @[User::vFormatFile]  +" -S "+ @[User::vSQLInstance]   +" -T"

Evolutes Expression

PracticeDB.dbo.tbl_StudentDetails IN E:\Preparation\SSIS\BCP\test.txt  -f E:\Preparation\SSIS\BCP\FmtStudentDetails.xml -S JOYDEEP-LAPTOP\JOYDEEPSQL12 -T

Step – 7  [  Run the Package and Observation ]

SELECT * FROM [dbo].[tbl_StudentDetails];

Hope you like it.

Posted by: MR. JOYDEEP DAS