how to check maximum dates in the csv files and insert into the sql tables using ssis

Question

how to check maximum dates in the csv files and insert into the sql tables using ssis

Raj9

Old Hand

Points: 333
More actions
October 29, 2010 at 8:37 am

#230482

ssis

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andrew gothard SSChampion Points: 12314 More actions · Answer 1

Personally I'd *probably* use a staging table for that, rather than do it in SSIS, particularly if you can set up an SP in the source which is appropriately indexed so you can exclude inappropriate rows from the query you're using to populate staging.

Hard to say, though, as there's little information to go on in the post.

If you can provide more information on your processes as per this excellent article http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ you're likely to get a lot more help as there's more for the top dogs on here to get their teeth into

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

viky2010 (10/29/2010)
how to check maximum dates in the csv files and insert into the sql tables using ssis.
Example
viky, 20-10-2009
raj ,29-09-2006
kiran,09-09-2005
maximum date is 20-10-2009
So only viky and 20-10-2009 rows has to go for sql server table using SSIS.

I agree with Andrew. This is a classic "chicken'n'egg" problem. You have to load all the data to figure out what the maximum data is so you can load the max data. A staging table is absolutely the way to do that.

Load all the data into a staging table, determine the max date from that, and then select the data that meets your max query to load into the "final" table.

As a side bar, I never load data directly into the "final" table from an outside source (like a file). It's too risky for a multitude of reasons. Load the data into a staging table, validate/preprocess the data, and only then move the data to the "final" table. I don't know if you'd call it a "best practice" but, for me, it's the ONLY way to load data from an external source.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrew gothard SSChampion Points: 12314 More actions · Answer 3

As a side bar, I never load data directly into the "final" table from an outside source (like a file). It's too risky for a multitude of reasons. Load the data into a staging table, validate/preprocess the data, and only then move the data to the "final" table. I don't know if you'd call it a "best practice" but, for me, it's the ONLY way to load data from an external source.

Can't agree too highly - just slinging data into a system from external sources without appropriate cleansing and transformation makes about as much sense as pogoing through a minefield.

Personal preference is:

Staging, generally an extract from raw tables

Transform, a halfway house combining the staging tables data with the columns in the fact / dimension. This is where you do your hash matching and setting up the ID values from the Dimensions, as well as any other transformatoins required to load your facts and Dims

Reporting - Facts and Dims

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

andrew gothard (10/30/2010)
As a side bar, I never load data directly into the "final" table from an outside source (like a file). It's too risky for a multitude of reasons. Load the data into a staging table, validate/preprocess the data, and only then move the data to the "final" table. I don't know if you'd call it a "best practice" but, for me, it's the ONLY way to load data from an external source.
Can't agree too highly - just slinging data into a system from external sources without appropriate cleansing and transformation makes about as much sense as pogoing through a minefield.
Personal preference is:
Staging, generally an extract from raw tables
Transform, a halfway house combining the staging tables data with the columns in the fact / dimension. This is where you do your hash matching and setting up the ID values from the Dimensions, as well as any other transformatoins required to load your facts and Dims
Reporting - Facts and Dims

Umm..... didn't we just agree here? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andrew gothard SSChampion Points: 12314 More actions · Answer 5

Jeff Moden (11/2/2010)
andrew gothard (10/30/2010)
As a side bar, I never load data directly into the "final" table from an outside source (like a file). It's too risky for a multitude of reasons. Load the data into a staging table, validate/preprocess the data, and only then move the data to the "final" table. I don't know if you'd call it a "best practice" but, for me, it's the ONLY way to load data from an external source.
Can't agree too highly - just slinging data into a system from external sources without appropriate cleansing and transformation makes about as much sense as pogoing through a minefield.
Personal preference is:
Staging, generally an extract from raw tables
Transform, a halfway house combining the staging tables data with the columns in the fact / dimension. This is where you do your hash matching and setting up the ID values from the Dimensions, as well as any other transformatoins required to load your facts and Dims
Reporting - Facts and Dims
Umm..... didn't we just agree here? 😉

Dude ... "Can't agree too highly". You were spot on, just one of those instances when one wants to reinforce a well made point 😛

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

viky2010 (10/29/2010)
ssis

That's nasty and very bad ettiquette. Why did you delete your original post?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)