i have 13 select queries that select record on a sql database, the result should be written an excel file. HOwever, whenever there is no data, i need "NO DATA" to be written to the excel file... i have 13 sheets in a excel file. what control flow and data flow should i use?

Thank you for the quick response. i already have a package in place, ole db connection ->data conversion -> xls destination. I tried putting a row count and contional split after ole db connection, however in 2nd case of condtional split where i need to put "no data" on the excel file, i dont know which object to select.

Sorry I still don't get it. I am new to SSIS and I am a DBA trying to automate a report. Can you it steo by step? Sorry!

Here is the scenario, i need to run an 13 SPs and paste the result to the 13 sheet of an excel file. If no data are extracted, the sheet should show the header, then the second row should have the words "NO DATA". Sample SP1 DECLARE @return_value int , @lvFromDate varchar(20) , @lvToDate varchar(20) SELECT @lvFromDate = CONVERT(VARCHAR(20),getdate()-1,1) , @lvToDate = CONVERT(VARCHAR(20),getdate()-1,1) EXEC @return_value = [dbo].[XXXXXXXXXX_SP] @FromDate = @lvFromDate, @ToDate = @lvToDate, @CarrierSearchId = 'XXX-DC' ,@AdditionalInsuredSearchID = N'000' I already have a package in placed, however it's direct ETL, (Ole DB Connection> Data Conversion > Excel Dstination) all for 13 SP. Can you help me step by step, because i am just starting SSIS. Thank you in advance

Here is the scenario, i need to run an 13 SPs and paste the result to the 13 sheet of an excel file. If no data are extracted, the sheet should show the header, then the second row should have the words "NO DATA". Sample SP1 DECLARE @return_value int , @lvFromDate varchar(20) , @lvToDate varchar(20) SELECT @lvFromDate = CONVERT(VARCHAR(20),getdate()-1,1) , @lvToDate = CONVERT(VARCHAR(20),getdate()-1,1) EXEC @return_value = [dbo].[XXXXXXXXXX_SP] @FromDate = @lvFromDate, @ToDate = @lvToDate, @CarrierSearchId = 'XXX-DC' ,@AdditionalInsuredSearchID = N'000' I already have a package in placed, however it's direct ETL, (Ole DB Connection> Data Conversion > Excel Dstination) all for 13 SP. Can you help me step by step, because i am just starting SSIS. Thank you in advance I would say the best way to handle that would be in the stored procedure itself, you could add something like: DECLARE @Cnt INT ,@return_value int , @lvFromDate varchar(20) , @lvToDate varchar(20) ,@Results varchar(20) SELECT @Cnt = count(*) FROM Table WHERE @FromDate = @lvFromDate, @ToDate = @lvToDate, @CarrierSearchId = 'XXX-DC' IF @Cnt <> 1 BEGIN @Results = 'No Data was returned' RETURN; END that's only an example MCITP - BI 2008 http://asqlb.blogspot.com/

The source is a production database, so we are not allowed to edit SPs.

Hi Jay, create a new column using derived column transformation in the DFT with the value "NO DATA" use rowcount and conditional split to check if any record is present in the source. In the conditional split check for the variables value configured in rowcount transformation Use 2 excel destination map only first column of the excel destination to the output from conditional split for "No Data" map the other output to corresponding columns Let me know incase of any doubt.

1) in a DFT take oledb source and the corresponding excel destination (u can select corresponding excel sheet). 2) use a rowcount transformation in the DFT and use a package variable "varRowCount" to store the count of rows in source. 3) map the columns from the source to the destination. Now this DFT is for normal data transfer when data is present in the source. The rowcount transformation will update the variable in it with the number of records in the source. 4) In the control flow level create another DFT and inside this DFT use script component as a source and same excel sheet as the destination with the same connection manager. 5) in the script component create one output column with the value "NO DATA". 6) map this column to the excel destination. One important thing is between the 2 DFT u need to add an expression wherein the control goes to the 2nd DFT only if the variable in the Rowcount transformation is equals to zero. let me know if it helped.