@dvlpr is correct your problem is you are getting NO results when Execute SQL Task 1 needs a single result.
The code you pasted is a little unclear as to which code is where but I will assume the first part is the code you use in SSIS Execute Task and the latter is an example in SSMS. If that is the case the problem is you are assigning the variable with a value of 0 in the script itself which I assume there is no PONUMBER that is 0:
Declare @POID as Varchar(50)
Set @POID = 0
WHERE (BizTalk_POA_HEADER.PONUMBER = @POID)
If you want to pass in the PONUMBER from your first dataflow task you need to load that to a variable and then use the variable in your Execute SQL task and made sure you setup parameter mapping correctly when doing so. here is one SO question on parameters that will help How to pass variable as a parameter in Execute SQL Task SSIS? And here is use of an expression task in a Data Flow task to set the variables value SSIS set result set from data flow to variable (note use the non-accepted answer that it was added later and was for 2012+ while the original was for 2008)
Next unless you are guaranteed only 1 result you will also need to add TOP 1 to your select statement because if you get more than 1 result you will get a different error again.
EDIT Per all of the comments:
So the configuration looks like you are using an ADO.NET connection which allows you to use named paramaters. There are restrictions if you don use that (https://msdn.microsoft.com/en-us/library/cc280502.aspx). The parameter mapping looks correct, and the result set should be fine. As far as your Error I don't know because you haven't posted the exact error so I cannot know what is the problem. If you use ADO.Net with your current Execute SQL Task configuration in the images you do have a couple of problems. 1 you are trying to declare the variable that you want to pass as a parameter that doesn't work, you need to remove that DECLARE statement. I suspect all you really need to do is modify your SQL Input to be:
SELECT DISTINCT BizTalk_POA_HEADER.PONUMBER, FAN_Suppliers.SupplierName,
FAN_Company_Details.CompanyName, FAN_Company_Details.[PrimaryEmail],
BizTalk_POA_HEADER.[DeliveryDate]
FROM BizTalk_POA_HEADER INNER JOIN
FAN_PO_Details ON BizTalk_POA_HEADER.PONUMBER =
CONCAT('PO',FAN_PO_Details.PoNumber) INNER JOIN
FAN_PO ON FAN_PO_Details.PurchaseOrderID = FAN_PO.PurchaseOrderID
INNER JOIN FAN_SupplierDetails ON FAN_PO.SupplierDetailsID =
FAN_SupplierDetails.SuppliersDetailsID INNER JOIN
FAN_Suppliers ON FAN_SupplierDetails.SupplierID = FAN_Suppliers.SupplierID
INNER JOIN FAN_Company_Details ON FAN_PO.CompanyID =
FAN_Company_Details.CompanyDetailsID
WHERE (BizTalk_POA_HEADER.PONUMBER = @POID)
Just get rid of the declare @POID and SET = 0 for a couple of reasons 1 because it is redundant when you have setup parameter mapping, 2 SSIS doesn't like it and will throw an error, 3 because you are setting a value of 0 to it which means it would always be 0.....
