Search code examples
ssisssis-2008

Implementing condition in SSIS

I'm importing data from txt to Sql Server table. That part works good.

Everyday this txt file is being deleted and new txt file is formed (i.e. yesterday there was data for 3 February, today for 4 February (column Date)).

When I run package, I want it to check whether Date column exists in database table. If it exists, skip, don't import, if it doesn't - import. And I want to save that Date value in a variable for further manipulations. How can I accomplish that?

Solution

  • we suppose you have your source file with the format and data as bellow 

    id    | product         |  dateLoad
1     |  dell           |  25-01-2016 16:23:14 
2     |  hp             |  25-01-2016 16:23:15 
3     |  lenovo         |  25-01-2016 16:23:16

    and your destination have the format as bellow 

    create table stack(id int,product varchar(20),dateLoad smalldatetime);

    In your SSIS add a Derived Column to convert the smalldatetime to date like this :

    Convert smalldatetime to Date

    Secondly add a Lookup in General Tab in your Lookup transformation Editor go to Specify how to handle rows with no matching entries and select Redirect rows to no match output. In Connection Tab add a connection to target table and i wrote a Sql query to convert the smalldatetime to date show the picture as bellow :

    connection to the target table

    In Column tab do this : enter image description here

    Finally add a connection with the lookup and your target table and select Lookup no matching output

    In the first execution i have 3 rowsinserted because i don't have the date in my table First execution

    I execute another time but i had 0 rows because i have the date in my table

    enter image description here

    I hope that help you