SSIS: How do I pull a SQL statement from a file into a string variable?

Question

I have a few SQL statements stored in text files. How do I pull these files into a string variable in SSIS so that I can use the same query in multiple places?

---

Answer to questions:

The queries are long and complex, something I'd prefer to edit in a real text editor, not inside the SSIS text boxes. I'd also like the queries to be editable by people who don't have access to SSIS or don't know how to use it. Finally, each of the queries is used in a number of different data flows. Correct me if I'm wrong, but if I use the same query in multiple spots I believe I have to either use a variable or re-write the code for each data flow.

Answer 1

I wouldn't recommend doing this. My suggestion is a stored procedure.

Here's how though, Create a file connection under connection manager. Open properties for the file connection, and edit the expressions. Create an expression for the connection string property that contains the SQL file path (something like @[User::SQLFileName] where SQLFileName is your variable).

Set up your execute SQL task and point it to the file connection containing the expression you just created. If you change the path in the variable it will change what SQL gets executed (since the expression is being evaluated at runtime). You could even load these paths from a db, and do a for each containing an execute SQL task. If you're loading data in/out using these files, and not just SQL tasks, you'll need to watch the columns and meta data.

Kris

Answer 2

Alternatively, (and this may not be applicable to yourself), you can create a view on the actual database which can be altered without access to the SSIS package. Other users can then edit the SQL like you mention and you might even get some intellisense support in the bargain.

I'm not a fan of writing SQL in SSIS text boxes either.

Answer 3

Here's how I did this very thing (after searching everywhere for an answer and finding none.)

I started with a package that the Export Data Wizard created, so my instructions relate to that. Doing it that way sets up the column mappings. If you didn't use the Export Wizard to create the package you may have to add columns by hand.

1. Add a string variable named SQLFileName to the package.
2. Add a string variable named SQLCommand to the package.
3. Add a Script Task at the start of the Control Flow.
4. Edit the Script Task and go to the Script section.
5. Add SQLFileName to the ReadOnlyVariables section. Set it to the path of your .sql file.
6. Add SQLCommand to the ReadWriteVariables section.
7. Click Design Script.
8. Paste in the script below. It just reads in the contents of the file specified by SqlFileName into SQLCommand.
9. OK your way out and connect the Script Task to the rest of your control flow.
10. Go to your Data Flow and select the Source Query. You'll need to edit the properties using the properties window. If you use the fancy Edit window you'll get an error about the command text not being set. It's because SQLCommand is blank at design-time.
11. Change the AccessMode to SQL Command from variable.
12. Under SQLVariableName, select SQLCommand.
13. You'll see that a red X gets added to the Source Query. That's because the SQLCommand is blank. To prevent the red X, change ValidateExternalMetaData to False.

That's about it. I hope I remembered all the bits. The script is the key part so that you can get the SQL into a variable and then use the variable in the Data Flow.

---

   Imports System
    Imports System.IO
    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

        Public Sub Main()

            Try

                Dts.Variables("SQLCommand").Value = System.IO.File.ReadAllText(Dts.Variables("SQLFileName").Value.ToString)

                Dts.TaskResult = Dts.Results.Success

            Catch oException As System.Exception

                Dts.TaskResult = Dts.Results.Failure

            End Try

        End Sub

    End Class