Implementing condition in SSIS

Question

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?

can you give us more information about the column of your `table `? or your `File` ? — user2460074, Feb 04 '16 at 17:25
@downloaddowload, txt file has several columns, one of which is date. And database has exact same number of columns where Date field is of type smalldatetime. Txt File can contain inside it rows with only one date (3rd February or 4th Faebruary and so on, with different times). What kind of information do you actually want me to provide? and just importing values from txt to table works fine. What I want is to set condition, so when I ran package today it imports all values for 4th of February,and if I ran it today second time,it doesn't import anything , since that data already exists in tbl — rakamakafo, Feb 04 '16 at 17:34
what's is the format of your date ? and also it's store in column with data type is `datetime` ? — user2460074, Feb 04 '16 at 17:36
@downloaddowload in txt it is like this 25.01.2016 16:23:14, and in table it is smalldatetime — rakamakafo, Feb 04 '16 at 17:37
@downloaddowload, normal tab delimited txt file. for instance, 25.01.2016 16:23:14 ABC XXX ZZZ. How can that help. Maybe I didn't get something, if it necessary I'll attachscreenshot — rakamakafo, Feb 04 '16 at 17:58

score 1 · Accepted Answer · answered Feb 05 '16 at 09:52

1

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 :

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 :

In Column tab do this :

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

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

I hope that help you

answered Feb 05 '16 at 09:52

user2460074

1,252
3
11
28

Thank you for detailed explanation. That worked great – rakamakafo Feb 05 '16 at 12:51
one more question: how can I save that Date in a variable. I want to use that in Send Mail Task in subject like: "Info for 04/02/2016. Thanks in advance – rakamakafo Feb 05 '16 at 13:06
1

do you want stock the date that exist in file in the `variable` in my example is `25-01-2016` ? – user2460074 Feb 05 '16 at 13:51
yes, exactly. Package runs, after data flow completed, I add Send Mail Task, and I wnat to use that 25-01-2016 for Subject like "Info for 25-01-2016". I don't know how to save that variable – rakamakafo Feb 05 '16 at 13:54
i think is better to do another question `How to save a string based in flat file into variable` – user2460074 Feb 05 '16 at 14:42
yeah, this interpretation is more valid – rakamakafo Feb 05 '16 at 14:53

Implementing condition in SSIS

1 Answers1