Hello, Not sure where my question should be in sql or SSIS forum. i have a table like.. source type importfilepath exportfilepath BOM 1 c:\import\BOM\BOM.xls c:\export\BOM\BOM.xls RTM 1 c:\import\RTM\RTM.xls c:\export\RTM\RTM.xls GSM 2 c:\import\GSM\GSM.xls c:\exmport\GSM\GSM.xls i want to use a stored procedure which would send an email attaching the file from the location in table column exportfilepath. I have an SSIS package with the 3 variables iBom, iRtm, iGsm and these variables would have values either 1 or 0, 1 or 0 and 2 or 0 respectively. so depending on those values i want to send file when BOM, RTM and GSM have 1,1 and 2 resp. from table above. can somebody tell me how do i write a script for that which would decide which source is valid and then pass the values from table and send email including the file. I already have the stored procedure to send email with file attachment for 3 sources but can i do that just in one script?

I would approach this like follows: Create an Execute SQL Task to get the data from the table and then pass its result as full resultset to a package variable. Then shred it in a ForLooop processing each row and passing the needed values to a parametrized SendMail Task (for attachment). See how you can shred the recordset: http://www.sqlis.com/post/Shredding-a-Recordset.aspx This way you do not need to script too much.Arthur My Blog

Hello ArthurZ, i am exactly using the same logic from the link you gave in your answer. I have foreach loop container, storing data from the table in variable dsConfig in execute sql task and have that variable in foreach loop container as a ADO object source variable, mapping source, type, importfilepath and exportfilepath in variablemaaping. In foreach i have execute sql having script, declare @source varchar(10) select @source = source from softdollardata If @source = 'BOM' select exportfilepath from softdollardata where source = 'BOM' else if @source = 'RTM' select exportfilepath from softdollardata where source = 'RTM' else if @source = 'GSM' select exportfilepath from softdollardata where source = 'GSM' else RAISERROR ('There is no error/or no data', 16,1) and have a variable exportfilepath in result set. I have one more execute sql task in Foreach to send email and has code. Exec [PWOSendDBMail] @TableName ='', @Body ='dataload done with errors. Attached is the error report', @BodyFormat ='HTML' , @CallerType = 'Prc',-- Job Level or Procedure Level[JOB/ PRC] @CallerName ='SoftDollarProcess', -- Name of the procedure or Job @FileAttachments = ? @ParentPackageName = '', @Success = 'Y' I have @FileAttachments = ? coz want to send file so trying to get the value of exportfilepath from parameter mapping I am getting error for variable dsconfig as Variable "User::dsConfig" does not contain a valid data object

To send the file use the SendMail Task SSIS canned component. There http://dwhanalytics.wordpress.com/2010/05/07/ssis-attach-multiples-files-in-send-mail-task/ you see that property that you need to set off the shredded recordset in the ForEach loopArthur My Blog

To send the file use the SendMail Task SSIS canned component. There http://dwhanalytics.wordpress.com/2010/05/07/ssis-attach-multiples-files-in-send-mail-task/ you see that property that you need to set off the shredded recordset in the ForEach loopArthur My Blog