Error in ExecuteSQl task 1

[developer155]

My ExecuteSQl task has this SQL

select distinct externalid, 0 as stauts

into tmpNewsLetterSync

from partnerlistrecips (nolock)

where ListID = ?

I am passing a parameter (mapped in Parameter Mapping to 0).
I am getting error when I try to parse the query:
The query failed to parse. Parameter Info cannot be derived from SQL statements. Set parameter information before preparing command

Any thoughts?

Thnaks

 

[manmaria]

Why don't you run and see.

 

[developer155]

does't the error mean that it will fail at runtime as well?

 

[gradley]

I've always had problems using the "?" parameter logic in DTS. It never parses for me unless it's the first statement in the script. It should in fact run correctly once you have your input parameters setup correctly.

You may have to fake out the script with a dummy select statement based on the parameters you are passing in

Ex of 2 parms:

Select * from employees where first_name = ? and last_name = ?

Make that the first statement, comment out the other ones, setup your input parms, then paste your original statement. You still won't be able to parse the script, but it will run for you.

Good luck!

 

[developer155]

One more thing:
I am trying to run a query that touches multiple db's on 1 server. SSIS gives me error saying that HouseholdNewsletter is nto associates with alias used in the query. That table is actually native to the db I am connected to. PushmailListGen is actually a different db. Whats going on???

UPDATE PushmailListGen.dbo.tmpNewsLetterSync
SET stauts = 1
FROM dbo.HouseholdNewsletter INNER JOIN
PushmailListGen.dbo.tmpNewsLetterSync ON dbo.HouseholdNewsletter.HouseholdID = PushmailListGen.dbo.tmpNewsLetterSync.externalid
WHERE (dbo.HouseholdNewsletter.NewsletterID = ?)

 

[gradley]

Is HouseholdNewsleter in the database that matches the connection string? Was it created by owner dbo, or is it a user table?

 

[developer155]

It does match the table in the connection string. PushmailListGen is actually a different db on the same server thats why I reference it my full name.
As for the owner, it is a user table and I did try removing dbo., but it also did nto help:

update PushmailListGen..tmpNewsLetterSync

set PushmailListGen..tmpNewsLetterSync.stauts = 1

from HouseholdNewsletter



where NewsletterID = ?

and PushmailListGen..tmpNewsLetterSync.externalid = HouseholdNewsletter.householdid

 

[gradley]

Actually the alias the message is referring to is the one you have qualified in the set statement I usually perform update statements as follows:

update PushmailListGen..tmpNewsLetterSync
set stauts = 1
from PushmailListGen..tmpNewsLetterSync a
join HouseholdNewsletter b
on <join table fields here>
Where a.NewsletterID = ?

 

[developer155]

Here is the message I am getting:

[Execute SQL Task] Error: Executing the query "update PushmailListGen..tmpNewsLetterSync set stauts = 1 from PushmailListGen..tmpNewsLetterSync a join HouseholdNewsletter b on a.ExternalID = b.HouseholdID Where a.NewsletterID = ?" failed with the following error: "The column prefix 'a' does not match with a table name or alias name used in the query.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

[gradley]

Is NewsletterID in the HouseholdNewsletter table? If so, the where clause should read:

Where b.NewsletterID = ?

 

[developer155]

Changed that... Now getting:
[Execute SQL Task] Error: Executing the query "update PushmailListGen..tmpNewsLetterSync set stauts = 1 from PushmailListGen..tmpNewsLetterSync a join HouseholdNewsletter b on a.ExternalID = b.HouseholdID Where b.NewsletterID = ?" failed with the following error: "The column prefix 'b' does not match with a table name or alias name used in the query.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

[gradley]

Does this statement work outside of SSIS (replacing the "?" with an actual value or variable)?

 

[developer155]

yep...I am really confused. Any other reason why SSIS doesnt like it?
And thanks for all your help, you seem like you know what you're talking about

 

[gradley]

I would verify your connection string from the General tab of the Execute SQl task in SSIS.

1. Are you using an OLE-DB Connection Type?
2. Verify that the connection is actually pointing to the database you believe it is.
3. Does the parameter mapping section have the right variable and type of variable defined?

As much as I try to avoid it, if the above questions don't correct the problem, you may want to try qualifying HouseholdNewsletter table to the right database.

Another option would be to create a Stored Procedure that contains this script and pass it in as a parameter. We had to do this on a number of occations because we were passing in multiple parameters that were not all used in the first statement.

Glad to help!

I hope you get this issue resolved.

 

[developer155]

I think I am almost there. What I tried is remove the Parameter and hardcode the value for NewsletterID like this:
update PushmailListGen..tmpNewsLetterSync
set stauts = 1
from PushmailListGen..tmpNewsLetterSync a
join HouseholdNewsletter b
on a.ExternalID = b.HouseholdID
Where b.NewsletterID = 11100

And it worked!

So it looks like the issue with parameter. The newsletterID is a variable of type INT32. What DataType should I use when I add it in ParameterMapping? Does that matter? I used Numeric. Maybe thats the issue? parameter name is set to 0, Direction to Input

 

[gradley]

You should probably use "LONG" or "SHORT" when adding to the ParameterMapping. How is the field defined in the database table? SSIS is more strict about data types than DTS was.

If you haven't tried them yet, Expressions are another way to code parameter queries. They are a little difficult at first, because they use the SSIS expression language, but work really well when you get stumped because you can concatinate the variable right into the expression.

Hope this helps!