?
Solved

Error In SSIS Package in Excute SQL Task

Posted on 2009-04-15
7
Medium Priority
?
3,587 Views
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:
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

0
Comment
Question by:passion420
7 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24152888
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
 

Author Comment

by:passion420
ID: 24153106
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24153785
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 24154360
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
 

Author Comment

by:passion420
ID: 24154558
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24155875
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
 

Author Closing Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 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