December 2, 2016 at 2:03 pm
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.
December 2, 2016 at 2:42 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 2, 2016 at 8:17 pm
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)
December 3, 2016 at 2:54 pm
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 4, 2016 at 6:56 pm
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
December 5, 2016 at 4:40 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply