In the mapping setting: map the respective input column to destination column.ġ2.In the connection manager setting: Select "OLE DB connection manager" name from the dropdown list, Select " Data access mode" to "Table or view - fast load", Select "Name of the table or the view" to "SSIS_Department".It will show the OLE DB destination editor. Right click on OLE DB destination and select Edit.Now place an OLE DB Destination to insert the department names. Specify any sort type and sort order and select "Remove rows with duplicate sort values" checkbox.ġ1. It will show the Sort Transformation Editor. Right click on Sort transformation and edit.Connect flat file source output to Sort transformation input. Now place a Sort Transformation from the SSIS tool box. Now we are going to deal with department name only. In the Columns setting: It will show all the available External Columns.In the connection manager setting: select the Flat file connection manager name in the dropdown.It will show the Flat File Source Editor. Right click on Flat File Source and edit. Now place a Flat File Source from the tool box.Right click Department data flow task and select Edit.If a record's RegisterNo with more than five characters, it will load into the error data table. Earlier we have included a validation, RegisterNo restricted to five characters. It means all the distinct DepatmentName available in the text file, insert into SSIS_Department table, then using a lookup function to find the DepartmnetId of the DepartmentName to insert the student details.Ĩ. But in our database design SSIS_Student table using DepartmentId. In this example we are going to load student data from text file. Finally click Ok button to create OLE DB Connection manager.ħ.Verify the connection established successfully or not by Test Connection button.Earlier we have created the tables in TestDB database. In "Select or enter a database name" select the "TestDB" database.Select "Use SQL Server Authentication" option and provide user name and password.Since I am using my local server I have used ".", otherwise need to specify the server name.In the connection manager window specify the Server name.Right click on Connection Manager Pane, create a new OLE DB Connection. Now let's create the OLE DB Connection manager. Then click Ok to create the connection manager.Ħ. Preview is used to view the sample file.Select RegisterNo column and set the OutputColumnWidth to 5. Here we are going to add a validation to RegisterNo column. Flat File Connection Manager Advanced settings: In the advanced settings, we can add validation.Flat File Connection Manager Columns settings: Columns setting will display all the available columns in the sample file.Flat File Connection Manager General settings: Specify a connection manager name, specify the sample file location earlier we created and leave the remaining settings.Right click on Connection Manager Pane and select New Flat File Connection. Create a Flat File Connection to read text file and OLE DB Connection to insert ext file data to database. For easy understanding change the first data flow task as "Department" and second data flow task as "Student".ĥ. Drag two data flow task from tool box to Control Flow Tab. Select Business Intelligence, from the Installed Templates option and project as Integration Services Project and give the Project Name.Ĥ. Start the SQL Server Data Tools (Microsoft Visual Studio 2010 Shell)ģ. CHECK CONSTRAINT Part 3: Create SSIS Packageġ. IDENTITY( 1, 1) NOT NULL,ĬONSTRAINT PRIMARY KEY CLUSTEREDĪLTER TABLE.
Set DepartmentId as a primary key and set Identity Specification is "yes"Ĭopy Code CREATE TABLE. Create a table "SSIS_Department" with the following columns Student Name, Register No and Department Name are the columns.ģ. In this example we are going to use a student, details text file. Let's Get Started Part 1: Create Sample Fileġ. Backgroundīefore starting this tip, I recommend you to read the previous post: SSIS - Import Text File Using the Tools and Code All the error records will stored in a separate table.
In this tip we will see how to import a text file with foreign key relation and validation by using SSIS.