Sending csv file using sql server 2014 based on condition

Question

Sending csv file using sql server 2014 based on condition

kpmandani

SSC Veteran

Points: 283
More actions
December 2, 2016 at 2:03 pm

#323807

I want to send a csv file using SQL Server only when it has more than 1 record present in it. Daily this file is getting cleared and updated with new records (if any comes). First row is appended with headers and so I want to achieve something of this kind:
if rowcount (file.csv)>1
{
send csv file as email
}
else
{
do nothing
}
end
I know there is store procedure called sp_send_dbmail in msdb but I do not know, how I can use this store procedure into my desired scenario.
Would be glad to receive any help on it.

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

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

kpmandani (12/2/2016)
I want to send a csv file using SQL Server only when it has more than 1 record present in it. Daily this file is getting cleared and updated with new records (if any comes). First row is appended with headers and so I want to achieve something of this kind:
if rowcount (file.csv)>1
{
send csv file as email
}
else
{
do nothing
}
end
I know there is store procedure called sp_send_dbmail in msdb but I do not know, how I can use this store procedure into my desired scenario.
Would be glad to receive any help on it.

Presumably you are populating the file using a data flow? If so, add a record count transformation into the data flow to save the record count to an int variable (which you will have to create first).

Use a precedence constraint (condition: RCVariable > 1) to control whether the e-mail gets sent or not.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kpmandani SSC Veteran Points: 283 More actions · Answer 2

Hi Phil,

So yes, records are being inserted into the csv file using an Informatica workflow. One out of the two targets is this csv file. Most of the time records go as insert and into the other target (database) table. But if there is an update in one particular column the record is sent to this csv file.

I won't go into the Informatica details more as m we are talking here about SQL and also my aim is to do this email part using SQL Sever.

Regarding your point on getting the row count from that transformation, although the Informatica software does have that feature of row count but that works well only when you have single target. For multiple targets, it won't be an ideal choice. Hence, I am trying to get the row count at and send the mail at the database level(SQL Server in my case)

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

OK. So you want SSIS to 'interrogate' a particular file, which has already been produced by another process, get the row count and then act according to result?

If so, your question boils down to: "How can I determine how many rows there are in a text file? Is that correct?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kpmandani SSC Veteran Points: 283 More actions · Answer 4

Not only just to determine the number of rows in the text file but based on the count, it should determine (and send) that same text file as an email.

So, if the count of rows is greater than 1, it should send that text file, if not, don't send it

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

kpmandani (12/4/2016)
Not only just to determine the number of rows in the text file but based on the count, it should determine (and send) that same text file as an email.
So, if the count of rows is greater than 1, it should send that text file, if not, don't send it

OK. Part 1 is the rowcount.

Create a Boolean 'SendFile' variable in your package.

Add a Script Task, with the above variable listed in the 'Read/Write Variables' list.

Your code will look something like this (untested):

int count = 0;

bool SendFile = false;

string line;

TextReader reader = new StreamReader("file.txt");

while ((line = reader.ReadLine()) != null)

{

count++;

if (count > 1)

SendFile = true

break;

}

reader.Close();

//Set the value of your package variable here

Once you have the Boolean variable populated, I assume you know how to use precedence constraints to control the logic flow?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel