Error In SSIS Package in Excute SQL Task

I have 2 exceute sql tasks in my ssis package:
Execute SQL Task---> Execute SQL task1--->For each container(contains Execute SQL task)

I am getting following error on execution:
Error:
[Execute SQL Task] Error: Executing the query "Select distinct Email from table1,table2 where condition1 and condition2 and table1.ID=table2.ID " failed with the following error: "The type of the value being assigned to variable "User::test" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Description of Different controls used in SSIS Package:
 
1. First Execute SQL task: It Updates table 1's one column based on some condition in another column in same table.
 
2. I have the Result Set set to Full Result set in Execute SQL Tak1 , Also in the Result set I have it defined as: Result Name as 0 and Variable Name: User::test
 
3. And For Each Loop. I am iterating through the ADO Object Source variable called User::test
 
4. Also in the Excute SQL task inside For Each Loop Container i have Parameter Mapping saying variable name and parameter name: 0

Open in new window

passion420Asked:
Who is Participating?
 
8080_DiverCommented:
COnnecting one SQL Task to another is a very common action . . , and the only thing they have to have in common is that they are in the same SSIS package.
As nmcdemaid said, if you are retrieving some value in your SELECT statement and assigning it to the User::Test variable, both the column from which you are retrieving it and the User::Test variable have to have the same datatype.  Otherwise, you will get exactly the type of error you have gotten . . . basically it is a data conversion error because you have said her's an apple and handed it an orange.
0
 
8080_DiverCommented:
What is the datatype of your User::test variable?
Also, I do not see where it is being used in your SQL statement.  Is it being used in one of your "condition1 and condition2" constraints.
If you open up the SSIS package in Visual Studio, you should be able to step through it (which may be how you captured the error message) and you should also be able to check the value for your variables (possibly setting up a Watch on them).
0
 
passion420Author Commented:
Hi
I was not using User:test anywhere, but now I changed the User: test to something which I use in my query.
As My query is: Select email....................

In my database table this email is of type varchar.
I also changed the variable in ForEach Loop container to User:Email, and variable mapping for this loop has Variable  Index set up as
       User:Email  0
       atrribute1   1
        attriibute   2
Sorry I am unable to see how to catch the values in visual studio, this error I cought by executing the paackage and going to the execution results and checking the error from there.

Do you think it may be due to one execute sql task is connect to another one

Execute SQL Task------> Execute SQL Task1

They both has nothing common, One just updates the column and then second one checks the condition and send email out.

Thanks for th reply !!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nmcdermaidCommented:
You need to answer 8080's question - what is the datatype of User:test? if its numeric then that would explain the error. You need to change it to a string data type
0
 
passion420Author Commented:
Hi 8080 Thanks!!
I got the Execute SQL task1 working, but now i m getting another error. Please if you can help me on this. I will accept your solution , if this gets resolved it will be really helpful.

[Execute SQL Task] Error: Executing the query "EXEC msdb.dbo.sp_send_dbmail @recipients = ? failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

When I changed this to few attributes it gave me another error.
[Execute SQL Task] Error: Executing the query "EXEC msdb.dbo.sp_send_dbmail @recipients = Email" failed with the following error: "The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks for your help, If I can resole this I will have my package get going i think. Please let me know if u know something about this, anyway  I will accept your solution. Thanks for your time.
0
 
PedroCGDCommented:
You dont have suficient permissions to execute some system stored procedures like sp_send_dbmail...
Also check this link abou SQL task:
http://www.sqlis.com/post/The-Execute-SQL-Task.aspx

Helped?
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
passion420Author Commented:
Thanks, please provide me some info for the erro m getting if you know something about this. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.