I need to get my rows to be inserted into the table so that it is picked up and tabulated properly. The excel table has a name in this case being the imaginative 'data'. This table is used to populate other sheets / pivot table stuff. I can get the data into the spreadsheet on the correct sheet BUT it puts the records outside of an 'Excel Table' leaving a blank but formatted table row. I have an excel template file which I am populating with data from a SQL Server database and table. When the package finishes executing, you can check the table to view the data from the CSV file.I have scoured the internet and can only find the basics which this appears not to be. 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.