Post reply

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

  • 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?!?!

  • November 10, 2016 at 5:03 am

    #1911603

    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.

  • November 10, 2016 at 6:17 am

    #1911637

    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!

  • November 11, 2016 at 8:28 am

    #1911924

    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.

    😎

  • November 11, 2016 at 8:37 am

    #1911927

    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]

  • November 11, 2016 at 8:45 am

    #1911930

    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.

    😎

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

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