Hi.. I have to load an excel file into temp table,But if the file contains 0 records then i should get a notification and package should be stopped. How is this possible in SSIS? How to do the Logging In ssis and send notifications? Please help me. Thanks

Hi - You could load the Excel file into your landing table and then check the row count after the data load process. You would create a package variable to store the results of the row count. Check that variable before your package proceeds and if the count is < 1, you would stop processing. As for sending notifications...do you mean by email? If so, you'll need to configure your sql server. At a high level: Create a Data Flow task that will load your excel file. Create a Execute SQL Task that will do your count on the temp table and store the results into a variable. Have a couple of Precedence Constraints on the SQL Task (above). One is for the Count > 1, which continues to process as normal One is for the Count <1, which will not process any further...do any logging, etc. Let me know if that helps. - will- will

Hi Will, your suggestion looks good however i don`t want to load the file into any destinantion with out checking the row count. According to your suggestion i have to load the excel file into a database table first and take row count from that . Is there any way we can write a script task to check the row count directly from excel file or do i need to write any macro with in excel to calculate the row count. Thanks

You can create two Data Flows - one to read the row count, and the second to perform the actual load. You'd use the same Excel Source in both. In the first Data Flow, you'd just run the Source into a Row Count transform, and populate an SSIS Integer variable. You'd add a Precedence Constraint with an Expression between the first and second Data Flows, requiring that the SSIS Integer variable be non-zero. In the second Data Flow, use the Source to insert into a Destination component, just like you have now. Add a Send Mail Task to the Control Flow, and add another Precedence Constraint from the first Data Flow to the Send Mail Task, but this time with an Expression requiring that the SSIS Integer variable be zero. Talk to me now on

Hi Daniel - yes, I would think that you could create a script task to handle the row count check first. Not sure if this one is a good example, but you can check it out: http://www.mail-archive.com/sqldownunder@list.sqldownunder.com/msg00125.html From my experience, it is always faster (from the dev perspective) to simply read the contents of the excel into a temp/landing table and then do the rowcount check. However, I would imagine that it can be handled via the script task too. - will

Go with Todd's suggestion - reading an excel file through a script task is fraught with danger including but not limited to varying excel versions and having to have MS Office or the interop assemblies available on the server that the package is executing fromRgds Geoff

Another option is to 1. Use one data flow to Pull the Excel data in through the data source 2. Then Use a multicast to get the row count and store it in a variable, and also export the data to a recordset destination in a variable 3. In the control flow add another data flow to put the data in the table from the record set with a prescendent constraint if the count variable is > 0

Hi Daniel , You can use C# code in script task to check the file size and set constarint using variable as below. string file = @"Filepath\\FileName.xlsx"; if (File.Exists(file)) { FileInfo filesize = new FileInfo(file); long size = filesize.Length; if (size > 100) { Dts.Variables["User::CheckFile"].Value = "Yes"; } else { Dts.Variables["User::CheckFile"].Value = "Wrongfile"; } else { Dts.Variables["User::CheckFile"].Value = "Nofile"; } }

Yet another option: use execute SQL task with Excel connection and take the count of rows. If its zero do not proceed with the further tasks.My Blog | Ask Me | Test your SSIS skills

Thank you very much for all your inputs.

Hi Daniel , You can use C# code in script task to check the file size and set constarint using variable as below. string file = @"Filepath\\FileName.xlsx"; if (File.Exists(file)) { FileInfo filesize = new FileInfo(file); long size = filesize.Length; if (size > 100) { Dts.Variables["User::CheckFile"].Value = "Yes"; } else { Dts.Variables["User::CheckFile"].Value = "Wrongfile"; } else { Dts.Variables["User::CheckFile"].Value = "Nofile"; } }

Hi Daniel , You can use C# code in script task to check the file size and set constarint using variable as below. string file = @"Filepath\\FileName.xlsx"; if (File.Exists(file)) { FileInfo filesize = new FileInfo(file); long size = filesize.Length; if (size > 100) { Dts.Variables["User::CheckFile"].Value = "Yes"; } else { Dts.Variables["User::CheckFile"].Value = "Wrongfile"; } else { Dts.Variables["User::CheckFile"].Value = "Nofile"; } }