Post reply

Need help setting variables in Script Task

  • June 11, 2012 at 2:04 pm

    #262748

    Hi all,

    I'm new to SSIS. I've created a data flow task that will import an Excel (.xlsx) file into a database on my SQL Server. The filename will be dynamic based on the current date. I have set a connection string as an expression containing some variables. The connection string evaluates perfectly using the default variable values I set.

    I want to be able to set the variable values using a Script Task. The script task is changing the variables correctly, but the updated values are not being read by the Data Flow Task. How do I map this correctly? Does it go:

    Script Task ----> Data Flow Task? Should they even be connected? The variables are global.

  • June 11, 2012 at 3:13 pm

    #1499539

    higgsboson99 (6/11/2012)

    Hi all,

    I'm new to SSIS. I've created a data flow task that will import an Excel (.xlsx) file into a database on my SQL Server. The filename will be dynamic based on the current date. I have set a connection string as an expression containing some variables. The connection string evaluates perfectly using the default variable values I set.

    I want to be able to set the variable values using a Script Task. The script task is changing the variables correctly, but the updated values are not being read by the Data Flow Task. How do I map this correctly? Does it go:

    Script Task ----> Data Flow Task? Should they even be connected? The variables are global.

    Yes they should be connected and it sounds like you are doing things right. Have you tried settting a few breakpoints on the tasks, just to verify 100% that everything is being set correctly?

  • June 12, 2012 at 6:14 am

    #1499733

    Here is the code I am using to set the variables. Am I assigning values correctly? (I know values themselves are correct.)

    Public Sub Main()

    Dts.Variables("ExcelSource").Value = "E:\myfile.xlsx"

    Dts.Variables("ExcelVersion").Value = "Excel 12.0 XML;HDR=YES"

    Dts.TaskResult = ScriptResults.Success

    End Sub

    I'm new to breakpoints and am having trouble viewing the output.

  • June 12, 2012 at 6:25 am

    #1499744

    higgsboson99 (6/12/2012)

    Here is the code I am using to set the variables. Am I assigning values correctly? (I know values themselves are correct.)

    Public Sub Main()

    Dts.Variables("ExcelSource").Value = "E:\myfile.xlsx"

    Dts.Variables("ExcelVersion").Value = "Excel 12.0 XML;HDR=YES"

    Dts.TaskResult = ScriptResults.Success

    End Sub

    I'm new to breakpoints and am having trouble viewing the output.

    Looks OK - assuming you have added those two variables to the read/write variables property of the script task.

    Breakpoints do not have to be difficult to use. Try this:

    1) Click once on your dataflow task.

    2) Press F9 (a red dot will appear on the dataflow task - you just created a pre-execute breakpoint)

    3) Run the package.

    When processing gets to the dataflow task, it will pause before executing it (hence 'pre execute') and a yellow arrow will appear inside the red dot I mentioned. At this point you should check your 'locals' window (which can be displayed by selecting Debug/Windows/Locals). There will be a Variables node there which you can expand to reveal all variables and their current values.

    --Edit Then press F5 to continue execution.

  • June 12, 2012 at 6:36 am

    #1499750

    Thanks, I got it working and am now able to observe the values being set in the Watch window. The backslashes are getting doubled up. Is this a problem? For example, my path becomes "E:\\myfile.xlsx" even though I have the string set to use only one backslash.

  • June 12, 2012 at 6:41 am

    #1499751

    higgsboson99 (6/12/2012)

    Thanks, I got it working and am now able to observe the values being set in the Watch window. The backslashes are getting doubled up. Is this a problem? For example, my path becomes "E:\\myfile.xlsx" even though I have the string set to use only one backslash.

    That's possibly a C# thing (where a backslash is an escape character) and is unlikely to be the problem, I would think.

    What error message are you receiving?

  • June 12, 2012 at 6:44 am

    #1499755

    I don't think I'm receiving any error message. Is it possible my control flow is not being run in the order I want it to? For example, right now I have a green arrow going from the script task to the data flow task. But when I try to run it with no default value for the variables in debug mode, it fails before even letting the script task complete. Also, the data flow task is higher up in the Progress tab. The timestamps are identical, however, so it's hard for me to tell what actually goes first. Does it sound like things are out of order?

  • June 12, 2012 at 6:46 am

    #1499757

    Can you post a screenshot?

  • June 12, 2012 at 7:21 am

    #1499784

    I got it working! I had to add another expression for ExcelFilePath to evaluate to my variable. After changing file names and deleting some rows, it works now! Thanks a bunch!

  • June 12, 2012 at 7:30 am

    #1499792

    I like happy endings, good work 🙂

Viewing 10 posts - 1 through 10 (of 10 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy