• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

SSIS question

Hi,

I migrated a DTS from 2000 to 2008, there is a task in DTS that takes 3 parameter values and pass it to a SQL statement, that basically looks like this
select * from table1 where p1=? and p2=? and p3=?

My question is, in order for me to do the samething in SSIS, which Comtrol Flow Item I should use?  thx
0
mcrmg
Asked:
mcrmg
  • 3
3 Solutions
 
HainKurtSr. System AnalystCommented:
is there any Execute SQL Task?
0
 
mcrmgAuthor Commented:
I did use Execute SQL Task, but I am getting
There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

this is the setting I have:
GENERAL
SQLsourceType:DirectInput
SQLstatement:select * from table1 where p1=? and p2=? and p3=?
ByPassPrepare: False  (set to true when parsing)


PARAMETER MAPPING
P1 INPUT LONG New Parameter 3
P1 INPUT LONG New Parameter 3
P1 INPUT DATE New Parameter 15

RESULT SET
empty

EXPRESSION
empty

thx
0
 
tigin44Commented:
open your ssis package...
declare 3 variables of type string for the parameters. We define then to be type of string to make easy the parameter passing operation.
declare a variable for the sql string of type string lets name it vSqlString.
while vSqlString is select ted on the variables list go to the properties page and click the elipsis near it. A window will open named Expression Builder.  There in the expressions  section construct a statement like this

"SELECT * FROM table1
WHERE p1 = " +   @[User::vparam1] +
  " AND p2 = " +  @[User::vparam2] +
  " AND p3 = " +  @[User::vparam3]

you can evaluate the string produced in the window... Here I assume that all the parameter of type alfanumeric. Otherwise some conversions should be done.

After completing the above procedures add a datasource to your dataflow task. Assign the database connection for the ole db datasource component and chose the Access Mode as "SQL Command From Variable" and SQL Command Variable as "vSqlString"

Now you can pass parameters and run your query with that parameters.


0
 
HainKurtSr. System AnalystCommented:
check here for a similar problem & solution

http://www.sqlservercentral.com/Forums/Topic322094-148-1.aspx

someone here says : when you have more than 1 record, that when you should select Full Result set, and then set the output to a variable of type OBJECT. then use this object as the input for a ForEach Loop, and do the mapping there similar to step1.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now