When the package finishes executing, you can check the table to view the data from the CSV file.In this article we are going to see how to use an Import Column transformation in SSIS.
Your screen should look like the image below. Drag the blue arrow from the source to the destination.ĭouble click on the destination and configure as shown below.Ĭlick on Mappings on the left side of the screen and ensure all fields are mapped correctly from source to destination.Ĭlick OK. Then drag the “OLE DB Destination” from the SSIS Toolbox to the “Data Flow” window and rename it as “SQL Table”. Click on Columns on the left side of the screen to review the columns in the file. You should now have the 2 Connection Managers at the bottom of the screen.ĭrag the “Flat File Source” from the SSIS Toolbox into the “Data Flow” window and rename it as “CSV File”.ĭouble click on this source and select the “Student CSV File” connection manager. You can test the connection by clicking “Test Connection” then click OK and OK again. Next, you need to setup the connection managers for both the CSV file and the SQL Server table, which are also known as source and destination respectively.Īt the bottom of the screen, under Connection Managers, do a right click and select “New Flat File Connection” and configure the Flat file connection manager as shown below.Įnter a suitable Connection manager name and specify the filepath for the Students.csv file.įor the table’s connection manager, do a right click again in the Connection Managers window and click on “ New OLE DB Connection”.Ĭlick on New and specify the Server name and database name that contains the StudentsDetail table. On the left side of the screen, in the SSIS Toolbar, drag the “Data Flow” to the “Control Flow” window and rename the task to “Load CSV File” On the right side of the displayed screen, in the “Solution Explorer” window, change the name of the default package to “Load CSV File into Table” You can check the “Create a directory for solution” box if you want to create a solution. Enter a name for project and a name for the solution, for example “Load CSV”.
Under the Business Intelligence group, select Integration Services and Integration Services Project. SA1 can copy this into Excel and save as a CSV file.Īfter launching Microsoft Visual Studio, navigate to File - New - Project, as shown below. Now create a CSV file with the data below. For this example, I used my ‘TrainingDB’ database. Run the script below in SQL Server to create the SQL table either on a new database or an existing one. First, you need to prepare the environment by creating the SQL Server table and the CSV file.
0 kommentar(er)
