SSIS help required

Question

SSIS help required

kapil_kk

SSC-Insane

Points: 21316
More actions
January 15, 2014 at 5:39 am

#150535

I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
In the SQL Task I've set the following
ResultSet = Full Result Set
Connection Type = OLE DB
Result Name = 0
I've created the following Variables with a Package scope
s1 as Object
s2 as Object
When I run the package I get the following errors
Foreach variable mapping number 1 to variable "User : : s1" cannot be applied
Any insight would be greatly appreciated.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
In the SQL Task I've set the following
ResultSet = Full Result Set
Connection Type = OLE DB
Result Name = 0
I've created the following Variables with a Package scope
s1 as Object
s2 as Object
When I run the package I get the following errors
Foreach variable mapping number 1 to variable "User : : s1" cannot be applied
Any insight would be greatly appreciated.

You don't need two variables of type object.

str1, str2 can be assigned to s1 as you have stated above.

You will then need to have two variables (with the appropriate data types so you can map str1 and str2) and configure the variables section in the Foreach Loop container like so:

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Abu Dina (1/15/2014)
kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
In the SQL Task I've set the following
ResultSet = Full Result Set
Connection Type = OLE DB
Result Name = 0
I've created the following Variables with a Package scope
s1 as Object
s2 as Object
When I run the package I get the following errors
Foreach variable mapping number 1 to variable "User : : s1" cannot be applied
Any insight would be greatly appreciated.
You don't need two variables of type object.
str1, str2 can be assigned to s1 as you have stated above.
You will then need to have two variables (with the appropriate data types so you can map str1 and str2) and configure the variables section in the Foreach Loop container like so:

sorry, didnt get u what you want to tell

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Abu Dina SSChampion Points: 14155 More actions · Answer 3

Abu Dina (1/15/2014)

kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable

The result of the above query needs to be stored in the User::s1 object (there is no need for s2!)

As you have said the Result Set section of the SQL Task will have Result Name = 0 And Variable Name = s1

You will also need two new variables (type string?!) User::str1 and User::str2

In the Foreach Loop Container

Collection: ADO object source Variable = User::s1 and choose rows in the first table

Variables Mapping section: Variable = User::str1 Index = 0 and Variable = User::str2 Index = 1

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

Abu Dina (1/15/2014)

Abu Dina (1/15/2014)
kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
The result of the above query needs to be stored in the User::s1 object (there is no need for s2!)
As you have said the Result Set section of the SQL Task will have Result Name = 0 And Variable Name = s1
You will also need two new variables (type string?!) User::str1 and User::str2
In the Foreach Loop Container
Collection: ADO object source Variable = User::s1 and choose rows in the first table
Variables Mapping section: Variable = User::str1 Index = 0 and Variable = User::str2 Index = 1

I tried the same steps that you told but still I am getting same error as before...

Error: The enumerator failed to retrieve element at index "1".

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Abu Dina SSChampion Points: 14155 More actions · Answer 5

kapil_kk (1/15/2014)

Abu Dina (1/15/2014)
Abu Dina (1/15/2014)
kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
The result of the above query needs to be stored in the User::s1 object (there is no need for s2!)
As you have said the Result Set section of the SQL Task will have Result Name = 0 And Variable Name = s1
You will also need two new variables (type string?!) User::str1 and User::str2
In the Foreach Loop Container
Collection: ADO object source Variable = User::s1 and choose rows in the first table
Variables Mapping section: Variable = User::str1 Index = 0 and Variable = User::str2 Index = 1
I tried the same steps that you told but still I am getting same error as before...
Error: The enumerator failed to retrieve element at index "1".

Okay, are we sure you have the below select in your Execute SQl Task?

SELECT str1, str2

FROM MyTable

And in your Foreach Loop Container in the Variable Mapping Sections do you have it starting at Index 0 for the first variable and 1 for the second? If that's how you've set it up then it should work!

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

Abu Dina (1/15/2014)

kapil_kk (1/15/2014)
Abu Dina (1/15/2014)
Abu Dina (1/15/2014)
kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
The result of the above query needs to be stored in the User::s1 object (there is no need for s2!)
As you have said the Result Set section of the SQL Task will have Result Name = 0 And Variable Name = s1
You will also need two new variables (type string?!) User::str1 and User::str2
In the Foreach Loop Container
Collection: ADO object source Variable = User::s1 and choose rows in the first table
Variables Mapping section: Variable = User::str1 Index = 0 and Variable = User::str2 Index = 1
I tried the same steps that you told but still I am getting same error as before...
Error: The enumerator failed to retrieve element at index "1".
Okay, are we sure you have the below select in your Execute SQl Task?
SELECT str1, str2
FROM MyTable
And in your Foreach Loop Container in the Variable Mapping Sections do you have it starting at Index 0 for the first variable and 1 for the second? If that's how you've set it up then it should work!

Thanks I missed one column name in Execute SQL task.....

Now, in For each loop I have another Execute SQL task which executes a Stored Procedure that need 2 parameters.

When I write like this: EXEC usp_office ?,?

I gives me error:

[Execute SQL Task] Error: Executing the query "EXEC usp_Office ?,?" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

kapil_kk (1/15/2014)

Abu Dina (1/15/2014)
kapil_kk (1/15/2014)
Abu Dina (1/15/2014)
Abu Dina (1/15/2014)
kapil_kk (1/15/2014)
I have created a foreach container to capture values from a query and run a script for each line. For some reason though I can't get past the variable mapping.
The SQL Task that is linked to the Foreach container is somthing like this:
SELECT str1, str2
FROM MyTable
The result of the above query needs to be stored in the User::s1 object (there is no need for s2!)
As you have said the Result Set section of the SQL Task will have Result Name = 0 And Variable Name = s1
You will also need two new variables (type string?!) User::str1 and User::str2
In the Foreach Loop Container
Collection: ADO object source Variable = User::s1 and choose rows in the first table
Variables Mapping section: Variable = User::str1 Index = 0 and Variable = User::str2 Index = 1
I tried the same steps that you told but still I am getting same error as before...
Error: The enumerator failed to retrieve element at index "1".
Okay, are we sure you have the below select in your Execute SQl Task?
SELECT str1, str2
FROM MyTable
And in your Foreach Loop Container in the Variable Mapping Sections do you have it starting at Index 0 for the first variable and 1 for the second? If that's how you've set it up then it should work!
Thanks I missed one column name in Execute SQL task.....
Now, in For each loop I have another Execute SQL task which executes a Stored Procedure that need 2 parameters.
When I write like this: EXEC usp_office ?,?
I gives me error:
[Execute SQL Task] Error: Executing the query "EXEC usp_Office ?,?" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks a lot for your help....

I have solved this another issue now, I forget to declare variable in parameter mapping....

Can you please tell me one thing that what is the use of the variable type Object?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Abu Dina SSChampion Points: 14155 More actions · Answer 8

Yes you will need to configure the Parameter Mapping section of the Execute SQL Task. You will need two entries in there so something like this:

Variable Name = User::str1

Direction = Input

Data Type = VARCHAR

Paremeter Name = 0

Parameter Size = 50 (don't know the size of your string)

Variable Name = User::str2

Direction = Input

Data Type = VARCHAR

Paremeter Name = 1

Parameter Size = 50 (don't know the size of your string)

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Abu Dina SSChampion Points: 14155 More actions · Answer 9

kapil_kk (1/15/2014)Can you please tell me one thing that what is the use of the variable type Object?

Think of it as a place holder for your query so it holds all the values from your table in the User::s1 variable

SELECT str1, str2

FROM MyTable

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn