SSIS: How to pull an SQL statement from a file into a string variable?

Question

I have several SQL statements stored in text files. How to transfer these files to a string variable in SSIS so that I can use the same query in multiple places?

---

Answer the questions:

The queries are long and complex, which I would rather edit in a real text editor rather than inside SSIS text fields. I would also like the requests to be editable by people who do not have access to SSIS or do not know how to use them. Finally, each of the queries is used in several different data streams. Correct me if I am wrong, but if I use the same query in many places, I believe that I need to either use a variable or rewrite the code for each data stream.

Answer 1

This is how I did it (after searching everywhere there is no answer and no search.)

, , . . , .

• SQLFileName .
• SQLCommand .
• Script .
• Script Script.
• SQLFileName ReadOnlyVariables. .sql .
• SQLCommand ReadWriteVariables.
• "" Script.
• Script . , SqlFileName, SQLCommand.
• Script .
• . , . , , . , SQLCommand .
• AccessMode SQL .
• SQLVariableName SQLCommand.
• , X . SQLCommand . X, ValidateExternalMetaData - False.

. , . Script , SQL , .

---

   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

Answer 2

. - .

, . . , SQL (- @[User::SQLFileName], SQLFileName ).

SQL , . , SQL ( ). db SQL. / , SQL-, .

Kris

Answer 3

Alternatively (and this may not apply to you directly), you can create a view of the actual database that can be changed without access to the SSIS package. Other users can then edit SQL, as you mention, and you can even get intellisense support in a transaction.

I am not a fan of writing SQL in SSIS text fields.