Error In SSIS Package in Excute SQL Task

Posted on 2009-04-15
Last Modified: 2013-11-10
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:
[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

Question by:passion420
    LVL 22

    Expert Comment

    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).

    Author Comment

    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 !!
    LVL 30

    Expert Comment

    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
    LVL 22

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 22

    Expert Comment

    You dont have suficient permissions to execute some system stored procedures like sp_send_dbmail...
    Also check this link abou SQL task:


    Author Closing Comment

    Thanks, please provide me some info for the erro m getting if you know something about this. :)

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now