Ssis: Read Excel File Using Falt File Connection Manager

This commodity demonstrate how to load data from Excel file into SQL Server table using SSIS.

Lets see step by step implementation of simple basic SSIS package which loads the information from Excel file into SQL Server table using SSIS.

As y'all can see here we take sample excel file which contains customer's information, this file volition be source file, and data volition exist loaded from source file to destination that will be SQL Server table.

Below is the SQL server tabular array, currently there are no records in SQL Server table.

Lets open up the SQL Server Information Tool (SSDT) and create a new package as shown beneath.

If you do non know how to create a SSIS project – refer Create a new SSIS project.

Now in Solution Explorer, select SSIS packages folder and right click and so so select New SSIS Parcel.

Once the parcel is created, requite it meaningful name every bit shown below.

Now become to Toolbox and select Data Flow Tas yard and drag it into Control Flow view as shown below.

Now double click on Information Flow Job or Correct click then select Edit.. from context menu, it will open a Data Flow view for selected Information flow task.

In one case you double click on Data flow task, y'all will be navigated to Information Menstruation view.

Now here we will add a Excel Source, and configure a connexion for excel file.

Lets get to Toolbox then expand Other Sources and select Excel Source component and elevate into Information Catamenia View as shown below.

Now double click on Excel Source.

Once you double click on Excel Source, a Excel Source Editor window opens, hither you tin select an existing connexion or can create a new connection director.

Lets create a new connection manager, click on New.. button, after that a new window Excel Connection Manager opens, now you need to select excel file path.

Too can specified excel file version, and if first row in your excel file contains column then continue the pick Start row has column names as checked else tin uncheck.

Lets select the excel file from location, then click on Open push button.

Subsequently that, yous volition see excel path, and excel version are specified. Now click on OK button.

Now you can see a connectedness is created, and in Data Access Manner yous will see four fashion as follows:

• • Table Or View: This option displays the list of available worksheet in excel file, and you accept to select desired canvass.
  • Tabular array name Or View proper name variable: In this option, yous need to specify the variable name which shop the tabular array or view name.
  • SQL Command: This choice allow you to specified the SQL control.
  • SQL Control from variable: In this option you can specified a variable name which store the SQL command.

Here nosotros go with Table Or View mode, so just go out information technology every bit is.

As nosotros have selected data admission fashion as Table Or View, so we need to select a worksheet from Name of the Excel canvas dropdown.

Once you lot done with this, you can click on Preview push button to see the data preview.

At present go to Column tab, you can verify the columns also tin can remove the column by unchecking the column.

After that in Error Output Tab, you can specified the behavior of the component in case of failure, you can specified whatsoever one option as follows:

1. • Ignore Failure: It ignores whatsoever failure while reading rows from source and the parcel will go on executing even whatsoever mistake occurred.
   • Redirect Row: It redirects the failed rows to other component which is connected with the error precedence constraints.
   • Fail component: It stops the execution of package in example of any failure.

We become with default selected value that is Fail component.

Now click on OK push button to complete the configuration for Excel Source.

You can also see a Excel connection managing director is created that can exist seen in Connectedness Manager window as shown below.

In case y'all want to rename the Excel source component then correct click on Excel source and select Rename from context menu.

Now we will add together a Destination component into Data menstruation view.

As our destination is SQL Server table and then we will add a OLEDB Destination, for this get to Other Destinations in toolbox tab and expand information technology and so select OLEDB Destination and Elevate it into Data Flow view.

Now volition add the path, means connect the Excel source component which is the source of path to the OLEDB destination component which is the end of path.

For this select Excel source component then drag the pointer and connect it to OLEDB destination as shown below.

A path connects two components in a information flow by connecting the output of one data menstruum component to the input of another component. It has a source and a destination.

One time you done it will wait similar as shown below.

Now double click on OLEDB Destination component.

Now double click on OLEDB destination component. Yous will encounter a OLEDB Destination Editor window opens.

Here you can select an existing connection or configure a new Database connections.

To create a new connection click on New.. button.

After that a Configure OLEDB connection Manager window opens, click on New.. button.

Once you click on New push button..  a connection Director window opens.

Hither y'all can specified a Server proper name, Hallmark mode , likewise tin can select Database name.

In one case you done with all these you can also verify the connection past clicking on Test connection push button.

After that just click on OK button.

In one case you click on OK button, you can run into a connection string is created as y'all specified.

Once y'all click on OK, you will get back to OLEDB connection Editor window.

In Connection Manger tab, you tin can select the table to which you want to load flat file information.

And then in Data access mode pick select as select table or view, and in Proper name of the tabular array or the view option select table name that is CustomeDetails.

Now in Mappings tab, you tin map the appropriate input columns with output columns. You can see by default source file columns that is input columns and destination tabular array cavalcade that is output columns are mapped automatically.

If whatever columns are not mapped correctly or missed and so y'all can rectify or mapped them manually.

Next, in Mistake output tab, you lot can specified the beliefs of the component in case of failure, which is same as we have discussed above when configuring the Excel source component.

Nosotros go with default value Fail Component.

At present click on OK button to complete the configuration.

Once yous done with this, yous volition meet a OLEDB connection manager is created in Connection Managers window.

Now our package is ready, lets execute the packet, right click on Data flow view and select Execute task from context menu.

Once you select Execute task, yous can see Package run successfully, and tin see 9 rows are loaded to SQL Server table.

Lets speedily check the SQL Server table, and yous can see at that place are ix rows.

Also Read..

Create a new SSIS project.

Load Flat file data into SQL Server table using SSIS

2,958 total views,  8 views today

collinsraveld.blogspot.com

Source: https://sqlskull.com/2021/05/05/load-data-from-excel-file-to-sql-server-table-using-ssis/

Share this post