Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Help needed with For Each Loop and Send Mail

Author  Topic 

v_kash
Starting Member

45 Posts

Posted - 2012-12-21 : 12:28:23
Hello: Any help would be greatly appreciated. To keep it simple, here's what we are trying to do. We have 2 excel files with STOREID as a column in each file. The values in file 1 are 9999 and the values in file 2 are 7100. We have a table in Sql Server called Weekly_Store_Emails_VK_Test. There are 2 columns (ID and email address). So there are 2 rows in this table (1 for ID 7100 and 1 for id 9999). New to SSIS, but I have a for each loop container to process thru the 2 files on my c drive, grab StoreID from each excel file, and then mapping that storeid to the ID in the table to grab the corresponding email address. However, something is not workign as I get both files sent to me! Error says = an error occurred while assigning a value to variable emailaddress. Single Row result set is specified, but no rows were returned'. I have no clue how to resolve this!!

I have 3 variables in this solution:

1) EmailAddress which has a data type STRING
2) StoreFile which has a data type STRING
3) StoreID which has a data type OBJECT

Please help!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 14:08:52
what task are you using to map the storeid to emailaddress?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

v_kash
Starting Member

45 Posts

Posted - 2012-12-21 : 14:43:31
an execute SQL task.
SELECT emailaddress
FROM dbo.Weekly_Store_Emails_VK_Test
WHERE (ID = ?

i have an execute SQL task as well in my excel connection manager. the sql statement for that is

SELECT StoreID FROM [Sheet1$]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 23:23:49
did you put a breakpoint at execute sql task to see if parameter value is getting passed correctly? Also check resultset variable and see if return value obtained ie emailaddress is correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -