Dynamic Cube Partitioning in SSAS 2008

In this article I am going to explain the Implementaion of Dynamic Cube Partition in SSAS 2008. I would want to split this article in two parts as mentioned below:

1. Partition benefits
2. Implementing Dynamic Partitions

Partition Benefits:

• Better Query Performance: Cube partition is a powerful mechanism for improving query performance. Queries that summarize data over 10 years could take considerably longer than those that only search through the current year data. If we have proper partitions then SSAS only has to scan a small subset of data to return query results hence dramatic performance improvements compared to queries running against a cube with a single partition.
• Minimize downtime:  Cube partitioning supports reducing downtime associated with cube processing. In almost all the cases, a portion of data warehouse is volatile and needs to be processed often. However other portions are relatively static. For example, in a sales cube, we need to change the current year's data nightly, but sales from previous years might change only occasionally - in case if account for merchandise returns and exchanges. If your warehouse tracks last 10 years salesthen processing only the current partition may be 10 times quicker than processing the entire cube.
• Aggregations benefits: The partition queried frequently could benefit from additional aggregations, which in turn could improve performance. Partition(s) that are used less can be processed less frequently with considerably fewer aggregations.
• Customized storage and processing settings: Frequently accessed partitions might benefit from proactive caching and ROLAP storage. On the other hand, other forms of storage and processing might be better for less frequently queried partitions.
• Distributed query and processing load: SSAS allows you to create remote partitions - a remote partition resides on a server different from its parent cube. This way the queries that affect the remote partition are processed on a server separate from its parent cube, allowing you to take advantage of additional processing power.
• Parallel Partitions Processing: SSAS allows processing multiple partitions in parallel on a server that has multiple processors. This can further reduce the total cube processing time.

 Implementing Dynamic Partitions

In this section I will explain step by step operations to create Dynamic Cube Partitions.

STEP1:
Preapare test data for Sales cube. I will use three dimensions (DimDate, DimProduct, and DimCustomer) and one fact table for Sales cube. Click here to download CubePartitionData.sql file which contains T-SQL code to generate these tables along with sample data.

STEP2:
Create new Analysis Services Project using BIDS and save it as Sales.

STEP3:
Create Data Source, and then Data Souce View using above three dimension tables.

If you are newbie, click Creating First Cube in SSAS 2008 for more information.

STEP4:
Create Sales cube  along with required dimensions. Set all  required properties of dimensions and create any hierarchies if required. My Solution looks like this:

STEP5:
Double click on the cube and navigate to Partition tab. You will see a default partition as shown below:

Delete this default partition and Click on new partition... to click new partition. In Partition Wizard, Select FactSales as available table in Specify Source Information page and click next. Select Specify a query to restict row in Restrict Rows page and write WHERE condition to restrict partition rows.

Click Next twice and enter Partition name Sales_20100101-20100131 in the Completing the Wizard page and click Finish.
Finally Process the cube. Once cube is processed successfully, you can see a new database in Analysis Services:

You can browse the data for this partition:

STEP6:
We are done with one manual partition. Now its time to create the partitions dynamically and apply a logic to process the delta data every time.

First off all, right click on the Partition Sales_20100101-20100131 (highlighted above)  and then select Script Partition as ==> Create To ==> New Query Editor Window. It will create XMLA scripts for this partition. Save this file in the project with CreatePartitionSales_20100101-20100131.xmla name.

In similar fashion, right click on the Partition Sales_20100101-20100131 and then select Process. This will open Process Partition wizard, click Script to generate XMLA script and save this file in the project with CreatePartitionSales_20100101-20100131.xmla name.

STEP7:

We are done with SSAS development! Now we have to create an SSIS package to implement dynamic creation of cube partition and processing of that partition.

Click on File => Add => New Project in menu bar in the same solution. Select Integration Services Project and enter the name of the SSIS Project.
Rename the SSIS package1.dtsx by DynamicSalesPartition.dtsx.

STEP8:
Add two connection Managers - one OLE DB connection manager for database and another Analysis Services connection manager for SSAS database.

STEP9:
Add following Package variables:

Here is the brief description of each variable:

Directory - To store package path
FromDateKey - To Store current partition start date key e.g. 20101201.
ToDateKey - To Store current partition end date e.g. 20101231
PartitionName - Current partition name e.g. SalesPartition_20101201-20101231
IsPartitionExists - To check whether partition alreday exists or not
CreatePartitionXMLA - To store XMLA script to create current partition
ProcessPartitionXMLA- To store XMLA script to process current partition

Note: You can add more variables as per need and business requirement.

STEP10

Now you have to develop SSIS package as shown below:



First Execute SQL Task is used to initialize FromDateKey, ToDateKey, and PartitionName for the current partition. Use below query in this Task:
DECLARE
   @FromDateKey varchar(8), @ToDateKey varchar(8),
   @CalendarYear int, @CalendarMonth int


SELECT
   @FromDateKey = MAX(FromDateKey),
   @ToDateKey = MAX(ToDateKey)
FROM PartitionLog (NOLOCK)


SELECT
   @CalendarYear = MIN(CalendarYear),
   @CalendarMonth = MIN(CalendarMonth)
FROM DimDate WHERE DateKey > @ToDateKey + 1


--SET FromDateKey and ToDateKey for current Partition
IF NOT CONVERT(varchar(8),GETDATE(),112) BETWEEN @FromDateKey AND @ToDateKey
SELECT @FromDateKey = MIN(DateKey), @ToDateKey = MAX(DateKey)
FROM DimDate (NOLOCK)
WHERE CalendarYear = @CalendarYear AND CalendarMonth = @CalendarMonth


SELECT @FromDateKey AS FromDateKey, @ToDateKey AS ToDateKey,
'Sales_' + @FromDateKey + '-' + @ToDateKey AS PartitionName
GO

Second Task in the package is Script Task. Here we need to read the contents of files
CreatePartitionSales_20100101-20100131.xmla and
ProcessPartitionSales_20100101-20100131.xmla and store in the package variables CreatePartitionXMLA & ProcessPartitionXMLA.

I am using User::Directory as ReadOnlyVariable and User::CreatePartitionXMLA,User::ProcessPartitionXMLA as ReadWriteVariables.


Click here to download the code for Script Task - Read XMLA Files and store in variables.

To know about reading file contents using Script Task, click Script Task to Read File.

Third Task is a sequence container which contains three task - Script Task, Analysis Services Execute DDL Task, and Execute SQL Task.

Script Task is used for two reasons -
1. To check whether current partition exists or not.
2. To update XMLA content for current Partition.

This is the most tricky and interseting part here. We need to replace Partition ID, Partition Name, and Source QueryDefinition for current Partition which are highlighted below:

In Script Task, select User::FromDateKey,User::PartitionName,User::ToDateKey as ReadOnlyVariables and User::CreatePartitionXMLA,User::ProcessPartitionXMLA,User::IsPartitionExists as ReadWriteVariables.

Click here to download the code for Script Task - Generate XMLA to create and Process new Partition.

Click SSIS Script Task to Find and Replace File contents to know more about find and replace mechanism using script task.

Second task in the sequence Container is Analysis Services Execute DDL Task. This task is used to create new Partition. In Analysis Services Execute DDL Task Editor, select SSAS.localhost as Connection, Variable as SourceType, and User::CreatePartitionXMLA as Source.

Also don't forget to set precedence constraint. You should execute this task only when partition does not exists. Open Precedence Constraint Editor and Select Expression and Constraint in Evolution operation, Success in Value, and @IsPartitionExists == 0 in Expression as shown below:

Third Task in the Sequence Container is Execute SQL Task - this is used to store newly created Partition information in the PartitionLog table. In Execute SQL Task, select ResultSet None, ConnectionType OLE DB, Connection (local).Test, SQLSourceType Direct input and SQLStatement as:

INSERT INTO dbo.PartitionLog
([PartitionName],[FromDateKey],[ToDateKey],[CreatedDate])
VALUES (?,?,?,GETDATE())

Click on Parameter Mapping and map the parameters as shown below:

STEP11
Finally we need to process the current partition. This is independent of previous sequence container where we are creating new partition if it is not exists.
However, we need to process the current partition every time we execute the package. In most of the cases, new partition will be created on the first day of month but partition should be processed everyday to include delta data.

This sequence container also contains three tasks.

First task is Analysis Services Processing Task - to process the dimensions data. Select SSAS.localhost as connection manager and all the dimensions in Object Name - which are not static in data. Select Process Update as Process Option.

Second Task is againg Analysis Serives Processing Task - to process Current Partition. Select SSAS.localhost as connection manager and Sales_20100101-20100131 in Object Name - which is default partition (or first partition). Select Process Full as Process Option.

Now click on Expression and select @[User::ProcessPartitionXMLA] in ProcessingCommands.

Finally store the Last Processed Date and Processed Count (number of time this partition has processed) in the log table using Execute SQL task. Select ResultSet None, ConnectionType OLE DB, Connection (local).Test, SQLSourceType Direct input and SQLStatement as:

UPDATE PartitionLog
SET LastProcessDate = GETDATE()
,ProcessedCount = ProcessedCount + 1
WHERE PartitionName = ?
 
Click on Parameter Mapping and map the parameters and map the parameter 0 with User::PartitionName.

STEP12
We are done with package development. Now its time to execute the package and check the dynamic behaviour of Partition creation and processing. Before execution the package, there is only partition in the SSAS Sale database.
Now Right Click on the package and execute it. Once package is execute successfully, you will see one more partition in the Sales database and one more entry in PartitionLog table.

 

Now Execute the package or schedule it to execute automatically. Once you are done with 10 more executions, you will reach to current month partition Sales_20101201-20101231. and you can see 12 partitions - one partition for each month as shown below:

Since you are in current month partition, if you execute the package every day in current month - it will not create any new partition till beginning of next month. It will only process Current Partition to include the data in the cube as shown below:

Now you can browse the data for all the partitions:

We are done with Dynamic Package creation.
I hope you will like this article and if your answer is yes then don't forget to click Like :-)

Many people have had asked me to share the solution. I have stored that in my SkyDrive so you can download it from here: Dynamic Cube Partition

Cheers!!!

 

SSIS Script Task to Find and Replace File contents

In my previous article Script Task to Read File I explained how to read the contents of a file and store in a package variable.
Few folks from my followers had been asked about Find and Replace mechanism using Script Task. In this article I am going to explain how to replace contents of a file.

I'll show following operations in this solution:

1. Read contents of .sql file and store in a variable.
2. Modify the WHERE condition of T-SQL query to fetch current months data
3. Store the contents back to the file.

Here you go:

STEP1: Create a new ssis package and add two package variable as shown below:

STEP2: Drag and drop Script Task and rename it as Script Task - Read File and then Find and Replace String. Double click on Script Task to open Script Task Editor, Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWrite Variables. Now Click on Edit Script... and copy & paste below code:

public void Main()
{
  String ErrInfo = "";
  String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
  try
  {
    String FileContent; //Variable to store File Contents
    FileContent = ReadFile(FilePath, ErrInfo);
    if (ErrInfo.Length > 0)
    {
       Dts.Log("Error while reading File " + FilePath, 0, null);
       Dts.Log(ErrInfo, 0, null);
       Dts.TaskResult = (int)ScriptResults.Failure;
       return;
    }

    DateTime currDate, StartDate, EndDate;
    currDate = DateTime.Today;
    StartDate = currDate.AddDays(-(currDate.Day - 1));
    EndDate = StartDate.AddMonths(1).AddDays(-1);

    //FileContent Before Replace;
    MessageBox.Show(FileContent);

    //Find and Replace --> Modify WHERE clause
    FileContent = FileContent.Replace(
       "D.DateKey BETWEEN 20080101 AND 20080131",
       "D.DateKey BETWEEN " +
       String.Format("{0:yyyyMMdd}", StartDate) + " AND " +
       String.Format("{0:yyyyMMdd}", EndDate)
    );

    //FileContent After Replace;
    MessageBox.Show(FileContent);
    Dts.Variables["User::FileContent"].Value = FileContent;

    //Write the contents back to File
    WriteToFile(FilePath,FileContent,ErrInfo);
    if (ErrInfo.Length > 0)
    {
       Dts.Log("Error while writing File " + FilePath, 0, null);
       Dts.Log(ErrInfo, 0, null);
       Dts.TaskResult = (int)ScriptResults.Failure;
       return;
    }
  }
  catch (Exception e)
  {
    Dts.Log(e.Message, 0, null);
    Dts.TaskResult = (int)ScriptResults.Failure;
  }
}

public String ReadFile(String FilePath, String ErrInfo)
{
  String strContents;
  StreamReader sReader;
  try
  {
    sReader = File.OpenText(FilePath);
    strContents = sReader.ReadToEnd();
    sReader.Close();
    return strContents;
  }
  catch (Exception e)
  {
    MessageBox.Show(ErrInfo);
    ErrInfo = e.Message;
    return "";
  }
}

public void WriteToFile(String FilePath, String strContents, String ErrInfo)
{
  StreamWriter sWriter;
  try
  {
    sWriter = new StreamWriter(FilePath);
    sWriter.Write(strContents);
    sWriter.Close();
  }
  catch (Exception e)
  {
    MessageBox.Show(ErrInfo);
    ErrInfo = e.Message;
  }
}

STEP3: We are done with package changes. Execute the package to test the output.

Messagebox for actual file contents

Messagebox after REPLACE (modified WHERE clause)

Now if you look at the actual file, the query has been modified as shown below:

Cheers!!!

Script Task to Read File

Problem: Reading a file using Script Task

Solution: I'll explain this by creating a new SSIS package, reading the contents of the file and store the results in a package variable using Script Task.
In this article, I have covered about how to Read File in Script using Visual Basic 2008 and Visual C#  2008. Below are the steps to achieve the goal:

STEP1: Create a new SSIS package (I have renamed it as ReadFile.dtsx) and add two package variables FilePath and FileContent as shown below:

FilePath is used to store the path of the file being read by script task. FileContent variable isa used to store the contents of the file.

STEP2: Drag & drop Script Task and double click to open Script Task Editor. Enter Script Task - Read File using C Sharp 2008 as Name in General tab. Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWriteVariables, and Microsoft Visual C# 2008 in ScriptLanguage in Script tab.

Note: Here I am using two script tasks to explain the solution in both Visual Basic and C# 2008.

STEP3: Click on Edit Script... button of Script Task Editor and paste following code:

using System.IO;
public void Main()
{
  String ErrInfo = "";
  String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
  try
  {
    Dts.Variables["User::FileContent"].Value = ReadFile(FilePath, ErrInfo);
    if (ErrInfo.Length > 0)
    {
      Dts.Log("Error while reading File " + FilePath, 0, null);
      Dts.Log(ErrInfo, 0, null);
      Dts.TaskResult = (int)ScriptResults.Failure;
    }
    else
    {
      MessageBox.Show(Dts.Variables["User::FileContent"].Value.ToString());
      //Here do whatever you want to do with file contents
      Dts.TaskResult = (int)ScriptResults.Success;
    }
  }
  catch (Exception e)
  {
    Dts.Log(e.Message, 0, null);
    Dts.TaskResult = (int)ScriptResults.Failure;
  }
}


public String ReadFile(String FilePath, String ErrInfo)
{
  String strContents;
  StreamReader sReader;
  try
  {
    sReader = File.OpenText(FilePath);
    strContents = sReader.ReadToEnd();
    sReader.Close();
    return strContents;
  }
  catch (Exception e)
  {
    MessageBox.Show(ErrInfo);
    ErrInfo = e.Message;
    return "";
  }
}

STEP4: Thats all. We are done with package development. The content of File will be stored in the package variable FileContent while executing the package. To test the output, execute this package, a message box will popup (I didn't comment the Messagebox in the code) as shown in below:

And here is the snapshot of actual file:

Below is the code for Visual Basic 2008.

Imports System.IO

Imports System.Text
 
Public Sub Main()
  Dim dataBytes() As Byte = Nothing
  Dim ErrInfo As String = "" 'Variable to store Error Information
  Try
    Dim FilePath As String = Dts.Variables("User::FilePath").Value.ToString
    Dts.Variables("User::FileContent").Value = ReadFile(FilePath, _
     ErrInfo).ToString()
    If ErrInfo.Length > 0 Then
      Dts.Log("Error while reading contents of file " & _
      FilePath, 0, dataBytes)
      Dts.Log(ErrInfo, 0, dataBytes)
      Dts.TaskResult = ScriptResults.Failure
      Exit Sub
    End If
    MessageBox.Show(Dts.Variables("User::FileContent").Value.ToString)
    Dts.TaskResult = ScriptResults.Success
  Catch Ex As Exception
    Dts.Log(Ex.Message, 0, dataBytes)
    Dts.TaskResult = ScriptResults.Failure
    Exit Sub
  End Try
End Sub


Public Function ReadFile(ByVal FilePath As String, _
Optional ByVal ErrorInfo As String = "") As StringBuilder
  Dim strContents As New StringBuilder
  Dim objReader As StreamReader
  Try
    objReader = New StreamReader(FilePath)
    strContents.Append(objReader.ReadToEnd())
    objReader.Close()
    Return strContents
  Catch Ex As Exception
    ErrorInfo = Ex.Message
    Return strContents
    Exit Function
  End Try
End Function

Dynamic SSIS Package to Import Excel files into SQL Server Database

Problem/Scenario
Need to import Excel files to a SQL table. Everyday one file is created at specified location in the source system. We need to copy that file from Source System to Local system and then load to SQL Table.

Conditions:
1.  Each file should be loaded only once. Everynight Job should be executed to load data into reporting Data mart.
2.  Source system will maintain all the history files so files at souce should not be deleted.
3.  If Job failed due to some reason (schema changes, server down, connection issues etc.), it should load all the files from last run date to current date in next successul run. For example, job didn't run last one week then whenever job runs next time successfully, it should load current file as well as all the files of last week which were missing.
4.  All the source files will have the same structure (Schema)
5.  Nomenclature - Each file will have name Transaction followed by current date in YYYY-MM-DD format. For example, if a file was created on 01-Aug-2010 then its name should be Transaction2010-08-01.

Solution
I will take advantage of the ForEach Loop Container. I'll create a new SSIS package to solve the above problem.

STEP 1:
Create following tables in your local database (destination database).
CREATE TABLE [TransactionHistory](
   [TransactionID] [int] NOT NULL,
   [ProductID] [int] NOT NULL,
   [ReferenceOrderID] [int] NOT NULL,
   [TransactionDate] [datetime] NOT NULL,
   [TransactionType] [nchar](1) NOT NULL,
   [Quantity] [int] NOT NULL,
   [ActualCost] [money] NOT NULL,
   CONSTRAINT [PK_TransactionHistory_TransactionID]
   PRIMARY KEY CLUSTERED ([TransactionID] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [ExcelFilesLog](
   [FileName] [varchar](100),
   [FileCreatedDate] [datetime],
   [FileLoadDate] [datetime],
   [RecordCount] [int],
) ON [PRIMARY]
GO

STEP 2: Create a new SSIS package and rename it to LoadDynamicExcelFiles.dtsx

STEP 3: Add following package variables:

VariableName	Description	Examle
LastRunDate	To store last run date	2010-07-30
CurrentDate	To hold running date	2010-08-10
Directory Source	To store source directory path	D:\SSIS\Hari\SourceFiles
Directory Destination	To store local directory path	D:\SSIS\Hari\DestinationFiles
CurrentFile Name	To store current file name	D:\SSIS\Hari\SourceFiles\ Transaction2010-08-01

Create one OLE DB connection (I will use (local).TestHN connection manager) for local database where you want to load excel files data. Create one Excel Connection Manager for excel files located in D:\SSIS\Hari\DestinationFiles. At least one file should be there to create excel connection manager.

Click on Excel Connection Manager --> go to Properties window --> Select Expression and set ExcelFilePath with package variable User::CurrentFileName as shown below:

For this article, I'll use two directories - one for sources files and one for destination files. Location of these files are given below:

Source Files: D:\SSIS\Hari\SourceFiles
Destination Files: D:\SSIS\Hari\DestinationFiles

I have created few excel files from Production.TransactionHistory table of AdventureWorks2008R2 database as shown below:

I have created files from 2010-07-20 to 2010-08-01. After executing the package first time, I will create files from 2010-08-02 to 2010-08-09 to test the package.

STEP 4:

Drag and drop File System Task and double click to open File System Task Editor. Enter FST - Delete destination directory content in name and  select Delete directory content as Operation. Set IsSourcePathVariable to True and select SourceVariable as User::DirectoryDestination. Finally click on OK and save changes.

Drag and drop Execute SQL Task and double click to open Execute SQL Task Editor. Enter Execute SQL Task - Get LastRunDate in Name, select Single row as Result Set, Conection Type as OLE DB and Connection as (local).TestHN and SQLSourceType as Direct input. Enter below query in SQLStatement:

SELECT ISNULL(MAX([FileCreatedDate]),'2010-01-01') AS LastRunDate
FROM [dbo].[ExcelFilesLog] (NOLOCK)

In Result Set tab, set Result Name 0 to variable User::LastRunDate.
Finally click OK and save changes.

Drag and drop Script Task and double click to open Script Task Editor. Select User::DirectoryDestination, User::DirectorySource, User::LastRunDate in ReadOnlyVariables. Click on Edit Script... and paste below code:

using System.IO; 

public void Main()
{
   try
   {
      string DirectorySource = Dts.Variables["User::DirectorySource"].Value.ToString();
      string DirectoryDestination = Dts.Variables["User::DirectoryDestination"].Value.ToString();
      DateTime LastRunDate = (DateTime)Dts.Variables["User::LastRunDate"].Value;


      string fileName,fileExtension;
      string[] files = Directory.GetFiles(DirectorySource);
      //Copy source files to destination
      foreach (string f in files)
      {
         fileName = Path.GetFileName(f);
         fileExtension = Path.GetExtension(f);
         DateTime CurrentDate = DateTime.Parse(fileName.Substring("Transaction".Length, 10));


         if ((DateTime.Compare(CurrentDate,LastRunDate)>0) && (fileExtension == ".xlsx"))
        {
            //MessageBox.Show(fileName.ToString());
            File.Copy(f, Path.Combine(DirectoryDestination, fileName), true);
         }
      }
      Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception ex)
   {
      Dts.Log(ex.Message, 0, null);
      Dts.TaskResult = (int)ScriptResults.Failure;
   }
}

Drag and drop Foreach Loop container. Select Foreach File Enumerator as Enumerator, enter D:\SSIS\Hari\DestinationFiles in Folder path and *.xlsx in files textbox. Select Fully qualified as Retrieve file name. In Variable Mappings, Select User::CurrentFileName for Index 0 to store current file name for each iteration.

Now drag and drop Data Flow Task inside Foreach loop container. Use Excel Source reader to read excel files from destination directory. Use Excel Connection Manager as connection manager for excel files. In connection properties, Select @[User::CurrentFileName] as ExcelFilePath. Use Data Conversion, if required. Use Row Count Task to count number of rows in data flow and store it in User::RecordCount variable. Use OLE DB Destination to load data into SQL table.

Drag and drop Execute SQL Task inside Foreach loop container to log information about current file.

Double click on Execute SQL Task to open Execute SQL Task Editor, enter Execute SQL Task - Insert info into Log table as Name, None as Result Set, OLE DB as connection type, (local).TestHN as connection, Direct input as SQLSourceType and below query as SQLStatement.

DECLARE

@FileName varchar(500)

,@FilePath varchar(500)

,@DestinationPath varchar(500)

SET @DestinationPath = ?
SET @FilePath = ?
SET @FileName = REPLACE(@FilePath,@DestinationPath + '\','')

INSERT INTO [ExcelFilesLog]
(
   [FileName]
   ,[FileCreatedDate]
   ,[FileLoadDate]
   ,[RecordCount]
)
SELECT
@FileName [FileName]
,CAST(SUBSTRING(@FileName,12,10) as datetime) [FileCreatedDate]
,GETDATE() [FileLoadDate]
,?

In Parameter mapping,  map User::DirectoryDestination, User::CurrentFileName, and User::RecordCount with parameter 0,1,2 respectively as shown below.

Finally click OK and save changes.

Below is the final layout of our package:

STEP 6:
We are done with the package development. To execute the package, go to SSIS package location, (in this example, it is D:\SSIS\Hari\Sample\SSIS-Sample1), right click on LoadDynamicExcelFiles.dtsx --> Open with --> SQL Server 2008 Integration Services Package Execution Utility. This will open Execute Package Utility. Click on Execute button to run ssis package. Now you can see the progress of package execution in Package Execution Progress window.

Now you can check ExcelFileLog table to cross check the result of package.

Now I'll add few more excel files in source location - from

Transaction2010-08-02 to Transaction2010-08-09 as shown below:

When I execute this package next time, it will load only new files. Yon can check ExcelFileLog for each iteration: