Yes Agreed to all your comments..

Let me switch back to basics and work on analyzing the data.

How about the - Having millions and millions of rows in a single partition

If we assume the data count for another 4 years we will be ending up with 3000...

Granularity of the data imports - - The granular level i see is day. You mean to go ahead with daily partitions.

After the partitions reaches certain limit delete...

Hmm..

I believe partitioning the table will boost up the performance for reporting purposes.

Will partition gain performance for data loads..?

Also, as the table is growing bigger and bigger, am...

For this reason the package is taking 4+ hours. If we estimate the table size for a couple of years, can the table be able to handle those many millions...

I understand.. The issue is, before we insert data into the table, we are comparing with the existing data.

Also, this table is used for reporting purposes etc..

To avoid the duplicate results use rank().

WITH CTEROLES

AS(

SELECT Name,Role,RANK() OVER( PARTITION BY Name ORDER BY Role) AS NumberofRoles FROM Sample

)

SELECT Name,NumberofRoles FROM CTEROLES WHERE NumberofRoles>1

Hi John,

I have taken a different for each loop container for each set of files. i.e 5 foreach loops for loading into 5 different tables which are of different structure....

Hi John,

It is said "You need to make sure that the collection for each loop only contains the files you want to be processed by the dataflow within that loop"...

Hi John,

The flow is - -

In the for each loop container i have just given to consider *.txt files from a specific folder. Under the for each loop i...

Hi John,

I have configured the for each loop to load all the .txt files from the folder. I am trying to explore the behaviour of for each loop. i.e...

The for loop will consider files in the below sequence - -

Test20110225

Test20110227

Test20110228

TestToday20110225

TestToday20110227

TestTodayt20110228

TestTomorrow20110225

TestTomorrow20110227

TestTomorrowt20110228

Data flow task 1 - 1,2,3 files

Data flow task 2 - 4,5,6 files

Data flow task...

Hi,

I am using a for each loop container and included 3 dataflow tasks inside it. The file structure is

--------------------------------

Test20110225, Test20110227, Test20110228 - These files contains 3 columns.

TestToday20110225, TestToday20110227, TestTodayt20110228...

Thanks a lot for your suggestion. I agree with you.

Hi,

Thanks for your suggestion. I just want to make sure whether the below approach is possible in SSIS

for ex: We are having 5 flat files with different metadata and...