Post reply

Use a user defined variable within TSQL task

  • June 18, 2009 at 8:13 am

    #135365

    I am using a TSQL Task to execute a procedure that includes the following Bulk Insert (amoung other things). Notice I am trying to use a variable I've defined in my package called "User::FoundFiles". Whats the proper way to refer to a variable inside a TSQL Task? Here's what I have that's not working:

    BULK INSERT #MTSA_PCS_RFO_temp FROM Dts.Variables("User::FoundFiles").Value

    WITH (ROWTERMINATOR = '"')

    Dave Coats

  • June 18, 2009 at 3:50 pm

    #1012100

    First create a ssis variable e.g. vSQL. Set the EvaluateAsExpression property = true for this variable and the expression put

    "BULK INSERT #MTSA_PCS_RFO_temp FROM " + @[User::vFileName] + "WITH (ROWTERMINATOR = '\"') "

    (Note you will have to excape the double quotes in the expersion)

    Now in your execute sql task change the SQLSourceType = Variable and set the sourcevariable to User::vSQL

    So now you are saying to execute the sql stored in this variable instead of directly input.

    HTH

  • June 22, 2009 at 7:37 am

    #1013286

    Thanks for the reply Mukti! Your suggestion worked great. I separated my Bulk Insert out into its own SQL Task (previously I had it in with the rest of my procedure). Now I'm executing it as a variable as you suggested. Thanks again.

    Dave Coats

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply