Amending a DTS package (Integration Service) to include a WHERE statement on a date field.

Question

Amending a DTS package (Integration Service) to include a WHERE statement on a date field.

andy.plumb

SSC Journeyman

Points: 98
More actions
November 10, 2016 at 1:33 am

#331618

Hi all
This is my first post here, so hello to you all!
I have a SQL 2008R2 server, which I have created various DTS packages to import data for an external (but still on our network) source.
The tables are being brought in fine.
However, one table has over 30million records, and I need to narrow this down as it's taking best part of 5 hours to complete.
here is what I've tried following some research.
I have right clicked the package and hit edit.
Then select the Data Flow tab, and under the Properties tab I've added a [Source - Query],[SqlCommand] which looks like this:
"SELECT * FROM U_Observation where UPDATED_DATE>2016-01-01"
It doesn't seem to matter how I put the date in (American format, in single quotes, double quotes, prefixed with getdate), it just completely ignores or throws up an error in relation to the date.
Any ideas?!?!

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

andy.plumb (11/10/2016)
Hi all
This is my first post here, so hello to you all!
I have a SQL 2008R2 server, which I have created various DTS packages to import data for an external (but still on our network) source.
The tables are being brought in fine.
However, one table has over 30million records, and I need to narrow this down as it's taking best part of 5 hours to complete.
here is what I've tried following some research.
I have right clicked the package and hit edit.
Then select the Data Flow tab, and under the Properties tab I've added a [Source - Query],[SqlCommand] which looks like this:
"SELECT * FROM U_Observation where UPDATED_DATE>2016-01-01"
It doesn't seem to matter how I put the date in (American format, in single quotes, double quotes, prefixed with getdate), it just completely ignores or throws up an error in relation to the date.
Any ideas?!?!

Quick suggestion, create a stored procedure with a where clause that filters by the date, i.e.

😎

SELECT

...

FROM ....

WHERE UPDATED_DATE > DATEADD(HOUR,-5,GETDATE())

Then call the procedure from your package.

andy.plumb SSC Journeyman Points: 98 More actions · Answer 2

thanks for the reply, I tried that and got error:

Description: An error occurred executing the provided SQL command: "SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR-2,GETDATE())". Native error : 0, [Transoft][TSODBC][usqlsd]superfluous (()

Is must be something in my syntax, but can't see where it's wrong!

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

andy.plumb (11/10/2016)
thanks for the reply, I tried that and got error:
Description: An error occurred executing the provided SQL command: "SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR-2,GETDATE())". Native error : 0, [Transoft][TSODBC][usqlsd]superfluous (()
Is must be something in my syntax, but can't see where it's wrong!

Can you post the DDL (create table) script including all indices, triggers, constraints etc.

😎

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

Eirikur Eiriksson (11/11/2016)
andy.plumb (11/10/2016)
thanks for the reply, I tried that and got error:
Description: An error occurred executing the provided SQL command: "SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR-2,GETDATE())". Native error : 0, [Transoft][TSODBC][usqlsd]superfluous (()
Is must be something in my syntax, but can't see where it's wrong!
Can you post the DDL (create table) script including all indices, triggers, constraints etc.
😎

SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR-2,GETDATE()) is missing a comma after YEAR. Try:

SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR, -2, GETDATE())

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Alvin Ramard (11/11/2016)
Eirikur Eiriksson (11/11/2016)
andy.plumb (11/10/2016)
thanks for the reply, I tried that and got error:
Description: An error occurred executing the provided SQL command: "SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR-2,GETDATE())". Native error : 0, [Transoft][TSODBC][usqlsd]superfluous (()
Is must be something in my syntax, but can't see where it's wrong!
Can you post the DDL (create table) script including all indices, triggers, constraints etc.
😎
SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR-2,GETDATE()) is missing a comma after YEAR. Try:
SELECT * FROM U_Observation WHERE UPDATED_DATE > DATEADD(YEAR, -2, GETDATE())

Good catch Alvin, thanks.

😎