how to check a DB column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

how to check a DB column

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-14 : 06:01:19

Hi everyone, would like to ask a question.

In my SSIS, i am required to check a DB column 'Batch_ID' for the current date. If it is the current date, i will have to do a data dump. However if it is not the current date i am required to send an email notification. Can anyone teach me how to do it? Step by step teaching will be appreciated alot. Thanks

Currently i have done the data dump using the data flow task. but i do not know how to do the 'checking of batch_id' and also email notification.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:20:30

the check has to be done inside data flow using conditional transform. then add two outputs for true and false part. in false part add a send mail task to send the mail and in true part add the destination task for data dump

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-14 : 10:02:05

wow i am not sure how to do this. Can you teach me how to do it please. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 10:47:37

see an example here

http://www.codeproject.com/KB/database/SSIS_ConditionalSplit.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-14 : 20:48:30

Thanks for giving me this article to help me. i have a question to ask. in the article, the conditional split have a code PlantType == "seasonal" and PlantType == "Perennial". i neeeded a code to get the current date. so how should i code it?

Edit: i have done the codes but i am not sure whether is it correct or not. can you check for me?

my codes are:

correctdate BATCH_ID == GETDATE()
incorrectdate BATCH_ID != GETDATE()
---------------------

If the codes are correct then how do i proceed with the next step?
do i have to link the conditional split to anything? cause i do not know what to link to next.

---------------------

would like to ask another question. is it possible to use a script task to do it?

like having a if..else statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 01:04:53

you have to link the incorrectdate and correctdate outputs to corresponding tasks which is send mail task for former and destination task for latter.

You can also use a script task but then you've configure destination and send mail tasks programtically

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 01:46:02

the incorrectdate and correctdate are in data flow but my data dump and email task are in control flow. so how do i link them together?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 01:47:51

how did data pump come in control task? what task is it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 02:07:27

in my SSIS, i have a script task (init variables) ---> data flow task (check for current date) --> execute sql task (Empty DB) --> data flow task (data dump). Also, data flow task (check for current date)--> send mail task if the check for current date is incorrect.

in the data flow task (check for current date), i have a OLE DB source which reads a DB and a conditional split which checks the DB for the current date.

i have done the conditional split. but i do not know how to link them next.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 02:44:52

all the data dump as well as conditional split should be inside same data flow task. that data flow task will be like OLE DB source --> conditional data flow-->output1(incorrect date)--> send mail task ,conditional data flow-->output2(correct date)--> your destination (oledb/flat file ...)
your control flow should be

script task (init variables)--> execute sql task (Empty DB) --> data flow task (data dump).

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 02:55:51

erm how do i put a send mail task in the data flow??

also, in my control flow, execute sql task (Empty DB) must come after the conditional split.

After ensuring that another DB(eg. source) is the current date, i go on to empty the DB(eg. test) and data dump the source DB to the test DB.

So how should i layout it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 03:07:41

your current date value comes in a column from source as i understand. then you need to check for each and every column rite? so what should be your rule? if at least one record is there for current date you do other activities? or is it like all data should be for current date before you do other activities (empty db, data dump etc)?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 03:14:43

the current date value comes from a column called batch_id. i want to check if all the DB's batch_id fields to be the current date (within the month). if all of the field is the current date, i want to empty the test db so that that is no fields in it. then i will data dump all the fields from source db to the test db.

If however there is one row not in the current date, i want to send an email indicating that the source db is not of the current version.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 04:01:15

If however there is one row not in the current date, i want to send an email indicating that the source db is not of the current version.
in that case do you still go ahead and do emptying as well as dump data for other rows which are in current date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 04:09:09

no i do not want to do that.

If my conditional split checks that the source db's batch_id is in the current date, i want it to empty the test db and then data dum the fields into test db. in this case, i do not want to send an email notifying me that source db is not in the current date.

However if my conditional split checks that the source db's batch_id is not in the current date, i want to send an email to me telling me that source db's batch_id is not in the current date. In this case, i do not want it to empty my test db and do a data dump.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 04:17:14

ok. then your package should have following layout

script task (init variables)--> execute sql task(count check)
this execute sql task will have statement like

SELECT COUNT(*) AS Cnt from yoursourcedbtable where batch_id=<current date value>
the following properties should be set
Resultset:Single row
In variable mapping map it to @[User::RecCnt] variable created in package

you will have two branches from this execute sql task as below
branch1
-------
execute sql task(count check)-->execute sql task (Empty DB) --> data flow task (data dump).

branch2
-------
execute sql task(count check)-->send mail task

for branch1 the link from execute sql task should have property values

Expression and Constraint
Constraint: On Success
Expression: @RecCnt!=0

for branch2 the link from execute sql task should have property values

Expression and Constraint
Constraint: On Success
Expression: @RecCnt==0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 04:26:30

erm for the codes..

SELECT COUNT(*) AS Cnt from yoursourcedbtable where batch_id=<current date value>

i just edit the 'yoursourcedtable' to the name i want and the rest i just leave it as it is?

i have to create a variable 'RecCnt' with the datatype as string?
where is the variable mapping? Is it the Parameter Mapping?

If yes, the variable name is User::RecCnt. but what is the direction, data type, parameter name, parameter size?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 04:39:14

nope. you need to replace =<current date value> with value (either hardcoded or from variable based on your reqmnt)

RecCnt should be of type int32

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 04:41:27

where is the variable mapping? Is it the Parameter Mapping?

If yes, the variable name is User::RecCnt. but what is the direction, data type, parameter name, parameter size?

-------

if i want the date value to be from a variable, how do i do it? Do i create a new variable and call it date. with data type datetime and value the day i choose? i want the date value to be the current month. i do not want the day.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 04:48:34

nope variable mapping is done in resultset tab

you need to assign the varable to get value from
@[System::PackageStartTime]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-15 : 04:49:40

if i want the date value to be from a variable, how do i do it? Do i create a new variable and call it date. with data type datetime and value the day i choose? i want the date value to be the current month. i do not want the day.

---------
so under the result set tab, the result name is
@[System::PackageStartTime] and the variable name is User::RecCnt?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources