SSIS Date Variable

Question

SSIS Date Variable

ssr1606

Old Hand

Points: 324
More actions
July 2, 2010 at 9:13 am

#147908

Hi ,
If the following question has already been answered please redirect me to that link.
I have a Dataflow task, in which , From source (Sql server table), using Select it will pick some records with some condition like where status_code = ?(variable) and Created Date = ? (variable) and insert those selected records into target table.
For everyday run the status will be 'abc' , and date parameter will be Getdate() , but some times if that package fails i have to change Status to " xyz" the date to some old date and run the package.
I am unable to understand 2 things
1) My created date is of datatype datetime, if i use getdate() in variable how i can compare those two dates.
2) Changing the date to some other old date.
If anyone can provide solution or direct me to the correct links, that will be a great help.
Thanks in advance.

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

I am not sure I understand your question? where does the create date come in and what or how are you looking to compare? there are lots of ways to compare a date are you looking to add a step to the transform or add something to a sql script?

you state earlier in your post that you change the date to an older date and then you are asking how to change the date to an older date?

Sorry it is friday and my mind may already be at the pool side.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

ssr1606 Old Hand Points: 324 More actions · Answer 2

Oledb source

sql statement -

SELECT

emp_id, cntc_id,..... created_by, created_date, updated_by, updated_date From

Employee

WHERE

Status_code= ? and

created_date = ?

Note: created_date will be datetime datatype, variable will be datetime too.

but to compare the created_date (which is of the form 2010-07-02 20:05:59.203) with the variable date which is get date() which will be dateand time at that particular moment (i mean seconds and minutes will be of that particular time.

-- change the date to an older one.

if i have to run the package to pick all the records whose created date is 2 or 3 days old, i need to change the date value right, so that is what i am referring to.

where created_date = 2010-06-30.

Dan.Humphries SSChampion Points: 12125 More actions · Answer 3

Sorry I still don't understand where your question is?

you can compare the creation date to getdate() directly in the sql statement you can even chage the creation date thhere based on the value. But I am not sure I understand what you wish to do with the value after you compare it.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

ssr1606 Old Hand Points: 324 More actions · Answer 4

Sorry if i am not clear abt what i am saying .

let me try one more time.

I am creating a package which will work as a batch job.

There are like 7 tables in Sql server from which data gets tranferred to the Oracle 7 tables (same structured).

I was talking about one of the Data flow task from the 7 which are almost same.

what do they do in that data flow task.

Oledb Source -

Using sql statement - Selects data using the following st.

SELECT

emp_id, cntc_id,..... created_by, created_date, updated_by, updated_date From

Employee

WHERE

Status_code= ? and

created_date = ?

Oledb Destination-

Table - Oracle table.

---

Status code will be - AAA

Date should be Getdate() - that day's data with the above code.

This is what happens in all the Data flow tasks.

--

Now , created_date in the table is for example 2010-07-02 20:05:59.203

and i set a variable (second question mark) to the date and give it getdate() as value.

while comparing

created_date = 2010-07-02 20:05:59.203 and soon.

getdate() = 2010-07-02 (20:05:59.203) -- this part will be different right.

so i have to compare 2010-07-02 part only from the created_date and date parameter .

but i can use some thing like this for created_date

where CONVERT(CHAR(10),created_Date,110)

but cannot do the following

where CONVERT(CHAR(10),created_Date,110) =

CONVERT(CHAR(10),?110)

------

this package will be scheduled to run everyday.

it has to pick that days data and load that into oracle tables.

- if i found out that some error occured in two days old load.

and i want to load that day's data again.

i have to pass that day's date to the date variable then run the batch right.

so i am unable all these with the date variable.

hope this will help you to understand my scenario.

and

thank you very much for your time and patience.

and

please let me know if you want any other information.

Thanks

ssr

srinivas.satti1 SSC Rookie Points: 39 More actions · Answer 5

what man no one ever came into a situation like this or not interested.

SouthernConfusion Mr or Mrs. 500 Points: 545 More actions · Answer 6

You will likely need to build your SQL Statement in a variable using expression language.

If you use expression language to create your sql statement you can manipulate the dates for comparison to whatever type needed.