locked
Multiple Executions of the Same Data Flow RRS feed

  • Question

  • I have an SSIS package that pulls from a SQL Oledb data source, which has variables that dynamically set the value of the parameters to execute the stored procedure.  The variables are also used to set the name of the output file for a flat file connection manager using an expression for file path.  Once the output file is created, is there a way to close both connections, update the parameter values and re execute the same data flow with different parameters?  I am trying to avoid using separate packages or a multicast, needs 16 flat file connections.

    I am a T-SQL Veteran but a bit of a newbie when it comes to SSIS.

    Thanks in advance

    Brent 

    Wednesday, December 23, 2015 8:05 PM

Answers

  • Hi Brent,

    If I understand correctly, you want to execute the same Data Flow Task with different parameter values.

    If in this scenario, we can directly change the variable value after the former execution, then it would pass new parameter values to the stored procedure and pass new values to the file path for the flat file connection manager.

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Thursday, December 24, 2015 8:20 AM

All replies

  • Hi Brent,

    Package Parameter is right choice.

    http://blogs.msdn.com/b/sqlgardner/archive/2015/06/18/ssis-tip-using-a-file-path-parameter-for-multiple-flat-files.aspx

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com


    • Edited by Navind Wednesday, December 23, 2015 9:54 PM
    Wednesday, December 23, 2015 9:54 PM
  • Brent

    Do you mean to execute not entire a package but only a data flow?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 24, 2015 6:36 AM
  • Hi Brent,

    If I understand correctly, you want to execute the same Data Flow Task with different parameter values.

    If in this scenario, we can directly change the variable value after the former execution, then it would pass new parameter values to the stored procedure and pass new values to the file path for the flat file connection manager.

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Thursday, December 24, 2015 8:20 AM
  • Yes, I just want to change the parameters that get supplied to the stored procedure, and then re execute the data flow with the new parameters for the stored procedure and file path. 

    Thanks, 

    Brent

    Tuesday, December 29, 2015 1:49 PM
  • Put the flow in a for loop. Set an exit variable.

    Reassign the values at the beginning of the loop, using a lookup.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Tuesday, December 29, 2015 3:00 PM