How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 3

Greetings reader.

Pre-requisite: This post is in continuation to series on “How to move data from Excel to SQL Server2008“- Act 1 Scene 2

After you have configured the excel connection manager, the next step is to set the actors for this play in existence. Meet “For each loop container”. You will find it in the Toolbox under “control Flow Items”. So you have to drag and drop it in the ‘control flow’ space as shown in as shown in Figure 13.1

Figure 13.1: Control Flow & For each loop containerFigure 13.1: Control Flow & For each loop container

For each loop container as the name suggests iterates or hops from one file to another file within the directory. Figure 13.2 shows the properties that you must set for this actor. Don’t forget to change the ‘Delay Validation’ property in Package, for each loop container from False to True.

Fig 13.2Figure 13.2: Package property

Also ensure that you have set the property of your project for “run 64 bit…” to False. By default its selected as ‘True’. This is shown below

Fig 13.2.0Fig 13.2.1 Now, within the for each loop container, you will now drag and drop the “Data Flow” task that is also in the “control flow items”. Don’t forget to change the ‘Delay Validation’ property in Data Flow container from False to True. Now double click on Data flow or alternatively you can click on the ‘data flow’ tab on the top of the screen. You will now see the Data Flow screen as shown below in Fig. 13.3

Fig 13.3Figure 13.3: Data Flow Task

Once you are in the Data Flow screen, next Drag and drop the Excel source on the blank screen. Remember you will not see the Excel source populated in the toolbox unless you are in the Data Flow screen. The same is shown in Fig 13.4

Fig 13.4Figure 13.4: Excel Source

To configure the excel source, you first have to double click it so as to open it. Its configuration is illustrated below in Fig. 13.5

Fig 13.5Figure 13.5: Configuring Excel Source

Important points to remember

  • Please ensure that you have set the value “True” for Delay Validation in For each loop container, Data Flow, and Excel Source and package properties.
  • Make sure you have set the disabled the run 64 bit runtime value to False.

If you were able to configure the excel source then you should see it without the red x as shown in Fig 13.6

Fig 13.6 Fig 13.6: Excel source configured

Next, you will drag and drop the flat file on the Data flow screen. As shown below in Fig 13.7

Fig 13.7 Fig 13.8

Fig 13.7: Configuring Flat File Destination

So now when you double click on the Flat file destination, you will see a screen like Fig 13.8

Fig 13.9.0 Fig 13.9

Fig 13.8 Configuring a Flat File destination

After you have click on OK button as shown above in Fig 13.8 you should see a screen like the following Fig 13.10 Configure it as shown and then click on the OK button. When you have done so, then you have to click on Mappings as shown below

Fig 13.11Fig: 13.9 Mappings warning

So you have to click on Mappings so as to make sure that the input column mappings are correct to the output column. The same is shown below

Fig 13.11.1Fig 13.10: Configuring Mappings

Once you click on Ok button you have successfully configured your Flat file destination. The same is shown in Fig 13.10.

Fig 13.12Fig 13.10: Source & Destination Configured Successfully

Great! You have successfully configured the source and the destination. Now when you run or execute the package you should see an output similar to the one shown below

Fig 13.13 Fig 13.13.1 Fig 13.13.2I hope you have enjoyed it so far. For I have equally enjoyed writing this post. Feel free to post your comments. Next post I will explain how to achieve the same process but this time the destination will be SQL Server. Till then stay safe and take care.

Advertisements

2 thoughts on “How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 3

Comments are closed.