SSIS question

Posted on 2009-12-25
Last Modified: 2013-11-10

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
Question by:mcrmg
    LVL 51

    Expert Comment

    is there any Execute SQL Task?

    Author Comment

    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:
    SQLstatement:select * from table1 where p1=? and p2=? and p3=?
    ByPassPrepare: False  (set to true when parsing)

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



    LVL 26

    Assisted Solution

    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.

    LVL 51

    Accepted Solution

    check here for a similar problem & solution

    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.
    LVL 51

    Assisted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now