Import a Table Using SSIS Package

We can use an SSIS package with C# or VB script to create a package to transfer a table from one SQL Server instance to another SQL server Instance.

The steps to create a new SSIS package are given below:-

1. Open Visual Studio and create a new Integration Services project.

2. Right-click on the project and select "New Package" to create a new SSIS package.

3. Select "Data Flow Task" & put it on the package design surface.

4. To open the Data Flow tab, double-click on the "Data Flow Task".

5. Now select an "OLE DB Source" component & put it on the design surface.

6. To open the editor, double-click on the "OLE DB Source" component.

7. Now we need to set a few parameters for the "OLE DB Source" component. OLE DB source requires a data source from which we need to fetch the data or a table & transfer the to a new SQL instance.

8. Now drag & drop an "OLE DB Destination" component on the design surface & set the same parameters for the destination server.

9. Open the editor & double-click on the "OLE DB Destination" component.

10. Set all required parameters of the "OLE DB Destination" to connect the target SQL Server instance and the destination table.

11. Now open the "Mappings" tab to map source and destination columns, if required.

12. Now click on the "OK" button to close the editor.

13. Now add another component i.e., "Script Component" on the design surface.

14. Link the "OLE DB Source" with "Script Component" and choose "Transformation" as the component type.

15. Now open the "Inputs and Outputs" tab.

16. Add an output column for each source column that we want to transfer.

17. Now open the editor of "Script Component".

18. Select "Input Columns" from the left-hand pane.

19. Select the input column one by one and click on the "Add as New Output Column" button.

20. Now we need to open the "Script" tab and then select scripting language like "C#" or "VB".

21. Use the below C# code

using System.Data.SqlClient;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    private SqlConnection connection;

    public override void PreExecute()

    {

        base.PreExecute();

        connection = new SqlConnection("Data Source=[TargetServerName];Initial Catalog=[TargetDatabaseName];User ID=[Username];Password=[Password];");

        connection.Open();

    }

    public override void PostExecute()

    {

        base.PostExecute();

        connection.Close();

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        SqlCommand command = new SqlCommand("INSERT INTO [TargetSchemaName].[TargetTableName] ([TargetColumn1], [TargetColumn2], [TargetColumn3]) VALUES (@TargetColumn1, @TargetColumn2, @TargetColumn3)", connection);

        command.Parameters.AddWithValue("@TargetColumn1", Row.SourceColumn1);

        command.Parameters.AddWithValue("@TargetColumn2", Row.SourceColumn2);

        command.Parameters.AddWithValue("@TargetColumn3", Row.SourceColumn3);

        command.ExecuteNonQuery();

    }

}

22. Now we need to replace the below details with your server, credentials details in the above code:

[TargetServerName], [TargetDatabaseName], [Username], [Password], [TargetSchemaName], and [TargetTableName] with the appropriate values for your target SQL Server instance and table.

23. Now click on the "OK" button to close the editor.

24. Save the SSIS package.

25. Try to execute this SSIS package locally & transfer the table from the source SQL Server instance to the target SQL Server instance.