none
Read only 3years of data from CSV file on base of date column loaddate into sql using SCRIPTTASK RRS feed

  • Question

  • Hi,

    I have past 5 years of data in CSV file.Everytime i want to read past 3 years of data i.e (Loaddate>=01-01-2017) from CSV file on base of loaddate and load into sql server.Please give me sample code using script task.My CSV file has millions of rows for each year.to decrease the loadtime want to read only 2 years of data from csv. Any help appreciated.

    Ex file:

    ID  Name LoadDate

    1     AAA   01-01-2015

    2     AAA   01-01-2016

    3     BBB   01-01-2017

    4     CCC   01-01-2018

    5     CCC   01-01-2019

    Expected output:

    3     BBB   01-01-2017

    4     CCC   01-01-2018

    5     CCC   01-01-2019

    Thanks,

    Su



    Wednesday, October 2, 2019 1:32 AM

All replies

  • Hi Suvanichikati,

    You could use Execute SQL Task to select the data of past 3 years.

     Please try the following codes.

    select * from table where LoadDate between '01-01-2017' and '12-31-2019'

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 2, 2019 6:11 AM
  • Hi Zoe , My source is csv not sql server. How can I use execute sql task for csv source. Thanks, Suvani
    Wednesday, October 2, 2019 11:28 AM
  • Hi Zoe , My source is csv not sql server. How can I use execute sql task for csv source. Thanks, Suvani

    Hi Suvani,

    Just upload the entire contents of the Excel file to a database table. This approach in ETL is called staging.

    Then you can apply the SQL query above. This also helps troubleshooting issues and track data movements. The table typically gets purged before loading.


    Arthur

    MyBlog


    Twitter

    Wednesday, October 2, 2019 1:33 PM
    Moderator
  • There is no way to limit the rows from the CSV file.  SSIS will always read the entire file.

    If you want only the last 3 years, you should fix your source CSV to only be 3 years.

    Otherwise, as Arthur said,  you need to read the entire file into a "stage" table and only "process" the last 3 years of records from the stage table.


    Wednesday, October 2, 2019 2:07 PM
  • Hi Suvanichikati,

    I seems that you already asked a similar question on the forum. And I showed how to treat a *.csv file on the file system as a virtual DB table and filter out not needed data via a single SSIS Execute SQL Task.

    Here is the link: How to read limited rows from CSV file in SSIS

    Wednesday, October 2, 2019 3:15 PM