locked
Passing value in file to stored procedure RRS feed

  • Question

  • Hello - I am trying to read a flat file that contains a number like 256395 for example. I want to store this as a variable then pass it onto a stored procedure to run then saves the results to a flat file. I am confused on how this would work any help would be greatly appreciated. Thanks.

    Tuesday, June 2, 2015 7:01 PM

Answers

All replies

  • See if this helps..

    Loop through each record in a text file


    Narsimha

    Tuesday, June 2, 2015 7:47 PM
  • I am trying to read a flat file that contains a number like 256395 : I assume you have only one text file, and one of the column name is 'ValuetoCheck', which will be used as a parameter to execute a stored procedure.

    Use 'Foreach Loop Container' for multiple text file to processes one text file at a time.

    What you need:

    1. Data Flow Task
    2. Flat File Source
    3. New Flat File Connection
    4. One Package Parameter [ Name: 'InputCriteria', Data Type: String], Sensitive: False, Required: True ]
    5. Conditional Split Transform
    6. Derived Column
    7. OLE DB Command
    8. New OLE DB Connection (If not exist)

    What you need to do:

    Load the text file in your SSIS Package (assuming you know how to add a Data Flow task and configure Flat File Source and Flat File Connection) , create one Package Parameter, for your criteria (256395), the value of this parameter will be passed at the time of executing this package. Add a Conditional Split and a Derived Column Transformation. 

    Double click on the 'Conditional Split' transform to open the 'Conditional Split Transformation Editor' and configure (see below). Click Ok to close.

    Output Name : ValuetoCheck equals InputCriteria

    Condition : [ValuetoCheck ] == @[$Package::InputCriteria]

    Drag the data path arrow (not red one) from the Conditional Split transformation to the Derived Column, select 'ValuetoCheck equals InputCriteria' from the Output list. Read more about Conditional Split.

    Double click on the Derived Column transform to open the Derived Column Transformation Editor, Drag the text file column (ValuetoCheck) to expression field, and configure as below:

    Derived Column Name: ParamValueForSPROC

    Derived Column: <add as new column>

    Expression: [ValuetoCheck]

    Now, add a OLE DB Command transform, drag the the data path arrow from the Derived Column transform to the OLE DB Command transform.

    To configure, double click on the OLE DB Command transform to open the Advanced Editor and configure. Under Connection Managers tab, select your OLE DB Connection.

    Select 'Component Properties' tab, in SQLCommand field type your stored procedure execution statement

    (i.e EXEC dbo.[YourStoredProcedureName] @YourStoredProcedureParameterName =?;)

    Select Column Mapping, and map Available Input Columns to Available Destination Columns (i.e. ValuetoCheck).

    Read more about 'How to use OLE DB Command'

    Hope this helps.

    Cheers

    Ronnie Rahman


    Please don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. web: www.ronnierahman.com

    Wednesday, June 3, 2015 8:38 AM
  • So file will always have a single value alone?

    Or are you trying to run stored procedure for each values in a column which is in the file and then store corresponding output to a file? If yes, you would require a looping logic inside SSIS


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, June 3, 2015 8:56 AM
  • Hi Emanlee00,

    Based on your description, I’m confused why did you need to pass a particular value from flat file to stored procedure? If you already know the value, we can directly pass the value to stored procedure; if you don’t know the value, what’s the rule to find the value?

    Supposing you have already find the value and store it to a variable, we can use Execute SQL Task to pass the variable value and execute the stored procedure. Please note that we need select Full result set as result set type, then use an Object data type variable in the Result Set pane. Then write the contents of the Full Result Set into a text file using Script Task. For more details, please refer to the following similar thread:
    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eb190bcf-a8f9-4ddd-adf9-8b036863db6c/writing-full-result-set-from-execute-sql-task-into-a-file-using-script-task?forum=sqlintegrationservices

    Reference:
    Result Sets in the Execute SQL Task

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, June 3, 2015 10:00 AM
  • So file will always have a single value alone?

    Or are you trying to run stored procedure for each values in a column which is in the file and then store corresponding output to a file? If yes, you would require a looping logic inside SSIS


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    File will have a single value that changes daily. That value will be passed to a stored proc then produce a flat file.
    Wednesday, June 3, 2015 4:24 PM