[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SSIS - Using a User Variable in a Query

Posted on 2010-04-06
9
Medium Priority
?
1,951 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:mkacos
  • 4
  • 4
9 Comments
 
LVL 16

Expert Comment

by:Megan Brooks
ID: 29972738
I think that parameter name in the 2nd screen shot needs to be just "0", not "Parameter0".
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 30002629
without the parameter the SQL query works?
Regards
0
 
LVL 1

Author Comment

by:mkacos
ID: 30014777
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 22

Expert Comment

by:PedroCGD
ID: 30018364
And the datatype you are using is the same that in Source?
Seems to eb some configuration problem...:-(
Give more details
0
 
LVL 1

Author Comment

by:mkacos
ID: 30019666
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
 
LVL 22

Accepted Solution

by:
PedroCGD earned 2000 total points
ID: 30019924
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
 
LVL 1

Author Comment

by:mkacos
ID: 30023027
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 30023602
IN spite of close the question set as answered for future user issues!
very good!!
regards,
Pedro
0
 
LVL 1

Author Closing Comment

by:mkacos
ID: 31711553
Sorry, I accepted the answer incorrectly.  Hopefully this is correct.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
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.
Viewers will learn how the fundamental information of how to create a table.

612 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