incrementing date time

Question

Post reply

incrementing date time

Charmer

SSChampion

Points: 14903
More actions
October 4, 2011 at 4:18 am

#258477

Hi,
i have a datetime column in excel sheet. i want to increment the date,month,year upto 2030. what should i do?
if it is possible by FOR LOOP container...what are the steps should i do to get that done?
Thanks,
Charmer

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

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

For...loop could do this,

are you able to explain a bit more about what you are trying to do?

Charmer SSChampion Points: 14903 More actions · Answer 2

i have a date column in an excel sheet...i need to take the value of the very first row of this column and i have to start incrementing the dat value upto the total count of the row...and i am inserting into the OLEDB destination...

for example....if the 1st row is 01/01/1980

Then it should come like 01/01/1980,01/02/1980,01/03/1980.......02/01/1980,02/02/1980......01/01/1985,01/02/1985...........upto the total row count in that excel sheet...

i follow mm/dd/yyyy format

Thanks,
Charmer

steveb. SSC-Forever Points: 46733 More actions · Answer 3

if you have a staging area in place to write queries you could do this by reading the start data into a varaible from excel,

then use this variable in combination with a Tally table (search this site for an example) and a DATEADD function as a data source. This would allow you to build a data flow that write to excel in one step in a set based method.

you could do this with a loop but it would mean writing to excel 65,000+ times (1 for each row) which would cause all sorts of issues.

Script tasks is another option but depending on how comfortable you are with VSA then it may be too complicated.

Also I cant help feeling that you could do this very easily with a piece of VBA, does it HAVE to be done in SSIS?

Charmer SSChampion Points: 14903 More actions · Answer 4

yes...

it must be done through SSIS...

i have a column name ISHOLIDAY in OLEDB..

also i need to check, if the datetime column value from excel sheet is january 1st or december 25th...

then ISHOLIDAY must be 0 else 1....

how can we check this in derived column or through some other dataflow component?

Thanks,
Charmer

steveb. SSC-Forever Points: 46733 More actions · Answer 5

prakash 67108 (10/4/2011)
yes...
it must be done through SSIS...
i have a column name ISHOLIDAY in OLEDB..
also i need to check, if the datetime column value from excel sheet is january 1st or december 25th...
then ISHOLIDAY must be 0 else 1....
how can we check this in derived column or through some other dataflow component?

how about my other questions/suggestions?

if you have a db then do this in the db it will be easy, looping a dataflow 65k+ times is not what you want to do.

Charmer SSChampion Points: 14903 More actions · Answer 6

steve...

i don't have any db..may be i can do..but i am creating this package for dynamic process....i have only the excel sheet...i need to give this package to the client...so its better if it is done through SSIS package...

Thanks,
Charmer