SSIS - Using a User Variable in a Query

I have read and read and read but still cannot come up with the answer.  I have attached three screen shots showing the way I have this setup but it does not work (see the third screen shot).  

I am converting data from SQL 2000 to SQL 2008.  All of our data are seperated by the [Customer Name].  I will be converting this data customer by customer.

I am trying to setup a package so all I have to do is change the value of GetCust and run the packages.  The third image show the error.  

GetCust is at the package level.  I know there is something simple that I am missing so please help.

OLE-DB-SOurce-Editor-1.jpg
OLE-DB-SOurce-Editor-2-.jpg
OLE-DB-SOurce-Editor-3-.jpg
LVL 1
mkacosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
I think that parameter name in the 2nd screen shot needs to be just "0", not "Parameter0".
0
PedroCGDCommented:
without the parameter the SQL query works?
Regards
0
mkacosAuthor Commented:
Pedro - If I hard code the query I.e. [customer name] = 'ABC' then it works.  If I use the variable then it times out (probably because there are 4 million+ records ) returning the data.  

RsCowden - I do not pick the parameter name, the system does.  If I change the name to 0 then it errors because of the name.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PedroCGDCommented:
And the datatype you are using is the same that in Source?
Seems to eb some configuration problem...:-(
Give more details
0
mkacosAuthor Commented:
I can define the datatype for the varable but I cannot for the parameter.  The datatype for the varable is string the data type for the column in the database is text.  

I am not sure what other details you need.  This is a new install on a new server.  There were no issues with the install that I saw.

What can I look for in the configuration?  
0
PedroCGDCommented:
in your SQL Statment at the source you can convert the parameter, or no?!

SELECT Field1 FROM MyTable WHERE convert(varchar(200), Field2)=convert(varchar(200), ?)

Never tried... try you and give feedback
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mkacosAuthor Commented:
Interesting;  this is what worked -- SELECT *  FROM [tbl_bills] WHERE  [customer name]=convert(varchar(50), ?)

However, it does not work when I preview it.  I get this error: TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
No value given for one or more required parameters. (Microsoft SQL Server Native Client 10.0)
-----------------------------

However, I can live with it although I would think it should work.  

Thanks Pedro.
0
PedroCGDCommented:
IN spite of close the question set as answered for future user issues!
very good!!
regards,
Pedro
0
mkacosAuthor Commented:
Sorry, I accepted the answer incorrectly.  Hopefully this is correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.