asked on

SQL SSIS: Create a variable from ODBC datasource

OK I am seeing several ways to create variable from OleDB datasource. but I have to pull a field (1 record field only) from Odbc and place it in a variable. I came across no solutions for this, and what I tried always demanded OLEDB Only..
Will I be forced to create a table for this one field in SQL Server so I pull it through OLEDB to get it into a varible.. That's a lame workaround.. Me says.. There MUST be a better way..

Come On Guys ... What is it!!!

Q-rider

Dear moosetracker

look at the sql execute task.
Set the result set to single value and assign the value from your statement to one of your defined variables.

Look at full description here: http://technet.microsoft.com/en-us/library/ms141689.aspx

Hope this helps...
Andreas

moosetracker

ASKER

Thanks, that sounds sweet and I am sure this would work for the normal population.. I was hopeful it would work for me, but I tried.. Problem is I am working with super crud DB (Great one to be cutting my teeth on with SSIS.. But my company likes to buy packages with super crud DB's (Got 2 more after this one).. then expects us to fix their mistakes..) I would have thought single row would only apply to the returning Query, but they must be trying to pass something to super Crud DB.. Because it causes it to cough and die.

Error is : [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".

But I put the same SQL statement into a dataReader and push to a flat file and only 1 row, 1 field is pulled.

Any other ideas??

moosetracker

ASKER

Whoops let me readdress the error.. I had deleted the Execute Statement, built the DataRead, then tried to recreate it to get the error to send to you.. I forgot to set the Variable.. When the Variable is set this is the error I get.

[Execute SQL Task] Error: An error occurred while assigning a value to variable "YearMth": "Value does not fall within the expected range.".

I tried changing the fieldtype from int to string to Object.. The originally passed field type is a 4-byte signed integer..

Q-rider

...you only have to use object variables when using full result set.
Can you please post your sql statement?
Don't worry - it took me ages before I understood the sql task... ;)

Best Regards,
Andreas

moosetracker

ASKER

select Last_month_end_ym from arcompany where Profit_no = 1

(We have 3 Records in table.. (3 companies).. But the Profit_no = 1 filters to the 1 company record.

I also attached the General setup and the ResultSet Setup
General.jpg
ResultSet.jpg

ASKER CERTIFIED SOLUTION

Q-rider

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

moosetracker

ASKER

Nope.. It errored out.. I worked it back to the error being on the as year_mth_val statement.. By taking the statement back from single to none, without the 'as year_mth_val' on it, it will run.. Add the 'as year_mth_val' and it gives me this error.

[Execute SQL Task] Error: Executing the query "select Last_month_end_ym as ym from arcompany where Profit_no = 1" failed with the following error: "[Unify][UnifyClient ODBC Driver][UnifyClient][UnifyLNA][Unify][Unify DataServer ODBC Driver][Unify DataServer]Syntax error in SQL dynamic statement. (1060)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This is strange because we have the same DB as a linked server in SQL.. And we can query with alias names..

Tried without the alias also everything else you suggested just using the real fieldname. (besides your suggested changes I also created a field of integer to load to because I was trying to add to a string, but the field is 4 byte signed integer).

I get this error

[Execute SQL Task] Error: An error occurred while assigning a value to variable "intYearmth": "Value does not fall within the expected range.".

Which checking the internet.. I saw someone suggested to use the column index of 0 in place of the name.. But then I get the error of..

[Execute SQL Task] Error: An error occurred while assigning a value to variable "intYearmth": "Result column index 0 is not valid.".

Q-rider

...it's a little bit confusing what microsoft made in there.
You have to use alias names to assign values in a single row case and '0' when using full result set.
When testing it on my machine it will work fine (as you surely believe ;) ).

The error is not clear to me... :( sorry

Reading a integer into a string variable should work.
Last idea is to escape the alias with 'x' or "x" - do you have a possibilty to run it on your db directly?

Is the 'ym' from above a typo?

I'm confused...

moosetracker

ASKER

Sorry.. No, I put the alias in & out so many times.. Sometimes I shortened the alias name from year_mth_val to ym. Regardless of the name.. put the alias in and hit the Parse Query button it will fail, take out the alias and it will Parse.

I am sure you are dead on with anyone that is not pulling from 'DB el crappo'.. My DB is hit and miss with both SSIS and SQL (more miss than hit), so I never know if it is my being green to SSIS or just 'DB el crappo'...

The db is retained by our 3rd party vendor so I can not run anything directly..

Could you give me an example of how to escape the alias with an 'x'.. I never heard of that, and don't quite know the proper syntax.

moosetracker

ASKER

I still have a problem with mine, due to DataBase 'el crappo. But this guy is dead on for any reasonable database. He was very helpful, patient and his advice was easy to follow.. He deserves the credit.