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
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.
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
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
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
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
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
Next, you will drag and drop the flat file on the Data flow screen. As shown below in Fig 13.7
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.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 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
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
Once you click on Ok button you have successfully configured your Flat file destination. The same is shown in Fig 13.10.
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
I 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.