Hi Neel, You could try this way: 1. In package, create variables: String User::ZZ_IncomingFile - for file path. Int ZZ_DBFileNotFoundCount - Select the count from database (If file not found). 2. In control flow , you need a script component and pass this two variables. ZZ_DBFileNotFoundCount should be writable.. Key logics here.. public void Main() { string sFilePath; string sPackagename; // Get the file path from SSIS Variables sFilePath = Dts.Variables["User::ZZ_IncomingFile"].Value.ToString(); try { // Check for existence of file if ( !File.Exists( sFilePath ) ) { //Dts.Variables["User::ZZ_DBFileNotFoundCount"].Value = (Convert to int )Dts.Variables["User::ZZ_DBFileNotFoundCount"].Value + 1; } } catch ( System.Exception e ) { } } 3. There're two components can connect to this script component, drag the 'Send Email Task' first. and click the green arrow, you will find a Precedence Constraint Editor there, choose Expression, then Expression show be: @ZZ_DBFileNotFoundCount > 5 That means if the count > 5, then the logic flow will go 'Send Email Task' this way. If count < or = 5, then will go another way. You just need to add a script component or File System Task or FTP task to delete the file. If you don't know how to delete a file, en..it's really very easy to do it and it won't take you so much time if you can goole it or find tutorial from MSDN. I didn't understand this "And then if the file has error message i need to send alret email to 5 persons." You mean you got error message when you loading flat file or just you find a error message from the flat file? If you find a error message from flat file, just send email to the person who need. If the person's information can from database table, just use a 'Execute SQL task' to query the address and save the addresses as a variable, then use 'For Loop Container' to get every address to call a 'Send Email Task' to send email to the specified person. If you don't know how to process a resultset by 'For Loop Container', just Google it, it's very easy to make it. Hope it helps.

There are always a lot of ways to solve a problem. "First" is not always even close to "best". In this case (and I really don't think this is controversial) the only portion of the original poster's problem that is not EASILY solveable with in-the-box SSIS tools is detecting whether the file exists or not. There isn't even a sliver of room for a script-only (or script-heavy) solution here. It's counterproductive because if the original poster is fluent enough in C# or VB.Net to solve the problem with Scripts without our further assistance... then they wouldn't be here trying to use SSIS. If they're not comfortable in code, then it's a choice whether you want to teach them how to code (in SSIS) or how to use the in-the-box objects. It seems obvious to me that you'd then want to teach "how do I use the tool the way it was intended" versus "how do I shoehorn this tool into working the way I (think I) want it to work". I've learned - the hard way - that doing the latter is a recipe for frustration, disaster, and rewrites. Talk to me now on

Hi, I am wondering how to do follwing requirement. My requirement is read the flat file and load into DB table. If i didnt find the file i need to update one counter column in DB table, if it reaches 5 i need to send email to users. After reading the file i should delete that file. And then if the file has error message i need to send alret email to 5 persons. I am not sure how to update the counter column if i didnt find the file? how to delete the file after reading? can i use any table for email address to send warning message? Please give your valuable answers. Thank you

Reading the content of flat file and taking action based on data ..can be performed with script component task .. In that we can update database table also, try using script component This link can also help you .. http://msdn.microsoft.com/en-us/library/ms345166.aspx Thanks and Regards, Sandhya

Thanks for the reply. If i give like the script below, working perfectly. public void <place w:st="on">Main</place>() { string sFilePath = @"J:\test\TandemSystemStatsSummary.txt"; if (File.Exists(sFilePath)) {Dts.Variables["User::Counter"].Value = 0;} else{} } But if i give the file path in variable like, FilePath = J:\test\TandemSystemStatsSummary.txt string sFilePath = Dts.Variables["User::FilePath"].Value.ToString(); It s not working. Could you please tell whats the wrong in this? In order to count how many times i couldnt find the file, i can't do it in script. Because every time pakage will run the count variable reset to default value. How will i hold the value? On possible i could create table in table and update a filed.I want to know other option.If i find Error message in text file i need to send email with whole file content. How can i attach whole file information into email? Other than attachements in email.

Are you getting some sort of error from the code? Is User::FilePath set up as package scope Is User::FilePath declared in your list of read only variables in the script configuration To keep your count you can set up a package level int variable - allow it to be read/write for your script task and then increment the variable value Chuck Pedretti | Magenic North Region | magenic.com

No i am not getting any error while giving file path in variable. The code is not not checking file exist correctly. If the file exist or not always else part ll be executed. Yes, User::FilePath is declared as package scope, its given as readable in script. To keep the count, i tried already what you suggested. But after the package ran, the count variable will reset to default value. If second time the file didnt find, the count value again one.

Sandhya, IMO the script component/task should be a last resort. If that's the only tool to use in SSIS, why use SSIS? Just code in C# or VB. Neel - where do you get information as to which file(s) to process? From a table? Talk to me now on

have a look at this: http://sql-articles.com/articles/bi/file-exists-check-in-ssis/ it may help you.Senior BI Consultant & PM @ Bicentrix If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

You don't need the "ToString()" at the end of that, you just need to cast it to string at the beginning, like this: string sFilePath = (string)Dts.Variables["User::FilePath"].Value; But that's splitting hairs... What you need to do is include a Dts.Events.FireInformation call so that you can see what the value you have in sFilePath is at runtime... Talk to me now on

You don't need the "ToString()" at the end of that, you just need to cast it to string at the beginning, like this: string sFilePath = (string)Dts.Variables["User::FilePath"].Value; But that's splitting hairs... What you need to do is include a Dts.Events.FireInformation call so that you can see what the value you have in sFilePath is at runtime... Talk to me now on

You don't need the "ToString()" at the end of that, you just need to cast it to string at the beginning, like this: string sFilePath = (string)Dts.Variables["User::FilePath"].Value; But that's splitting hairs... What you need to do is include a Dts.Events.FireInformation call so that you can see what the value you have in sFilePath is at runtime... Talk to me now on

Hi Neel, You could try this way: 1. In package, create variables: String User::ZZ_IncomingFile - for file path. Int ZZ_DBFileNotFoundCount - Select the count from database (If file not found). 2. In control flow , you need a script component and pass this two variables. ZZ_DBFileNotFoundCount should be writable.. Key logics here.. public void Main() { string sFilePath; string sPackagename; // Get the file path from SSIS Variables sFilePath = Dts.Variables["User::ZZ_IncomingFile"].Value.ToString(); try { // Check for existence of file if ( !File.Exists( sFilePath ) ) { //Dts.Variables["User::ZZ_DBFileNotFoundCount"].Value = (Convert to int )Dts.Variables["User::ZZ_DBFileNotFoundCount"].Value + 1; } } catch ( System.Exception e ) { } } 3. There're two components can connect to this script component, drag the 'Send Email Task' first. and click the green arrow, you will find a Precedence Constraint Editor there, choose Expression, then Expression show be: @ZZ_DBFileNotFoundCount > 5 That means if the count > 5, then the logic flow will go 'Send Email Task' this way. If count < or = 5, then will go another way. You just need to add a script component or File System Task or FTP task to delete the file. If you don't know how to delete a file, en..it's really very easy to do it and it won't take you so much time if you can goole it or find tutorial from MSDN. I didn't understand this "And then if the file has error message i need to send alret email to 5 persons." You mean you got error message when you loading flat file or just you find a error message from the flat file? If you find a error message from flat file, just send email to the person who need. If the person's information can from database table, just use a 'Execute SQL task' to query the address and save the addresses as a variable, then use 'For Loop Container' to get every address to call a 'Send Email Task' to send email to the specified person. If you don't know how to process a resultset by 'For Loop Container', just Google it, it's very easy to make it. Hope it helps.

Hi Todd, I didn't mean script component is the only way to perform it , but script component can also be used for reading file. Thanks and Regards, Sandhya

Hi Sandhya, As Todd as mentioned, implement custom coding only when no other task or combination of tasks can yield an optimal design. The idea is not to invent the wheel and to leverage the best benefits of .NET encapsulation. For reading the file, incase of .txt or .tsv or .csv I think Flat file source meets most common scenarios. To delete file: File System Task can be used. Why use File.Exists()? For sending email: Send mail task(default) can be used. Why use SMTP classses in .NET? An imporper code program wil only make the ETL more vulnerable.Please vote as helpful or mark as answer, if it helps Cheers, Raunak | t: @raunakjhawar | My Blog

I think Neel's problem is which way he can try and solve the problem first but not which is the best way. He can try to use more efficient way once he solve the problem. So I don't think there's any problem in Sandhya's suggestion.

Thanks for your replies. I solved the problem.