3

My SSIS projects tend to run queries that require changes as they move between environments, like the table schema might change or a value in the Where clause. I've always either put my SQL into a Project Parameter, which is hard to edit since formatting is lost, or just put it directly into the Execute SQL Task/Data Flow Source then manually edited it between migrations which is also not ideal.

I was wonder though if I added my SQL scripts to files within the project, can these be read back in? Example if I put a query like this: select id, name from %schema%.tablename I'd like to read this into a variable then it's easy to use an expression as I do with Project Parameters to replace %schema% with the appropriate value. Then the .sql files within the project can be edited with little effort or even tested through an Execute SQL Task that's disabled/removed before the project goes into the deployment flow. But I've been unable to find how to read in a file using a relative path within the project. Also I'm not even sure these get deployed to the SSIS Server.

Thanks for any insight.

I've added a text file query.sql to an SSIS (SQL 2017) Project in Visual Studio, bit I've found no way to pull the contents of query.sql into a variable.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Sam Alex
  • 79
  • 2
  • 11
  • Have you considered decoupling the ssis package from the sql ? You could move the sql to a stored procedure(s) and then call the sql from the package. You could even add a flag in a table somewhere in the DB that determines which environment the stored procedure is running. The proc could read flag and set the variables automatically in the stored procedure without you having to change them every time manually. – camba1 May 30 '19 at 05:56

2 Answers2

3

Native tooling approach

For an Execute SQL Task, there's an option to source your query directly from a file.

Set your SQLSourceType to File Connection and then specify a file connection manager in the FileConnection section.

SQLSourceType = File Connection

Do be aware that while this is handy, it's also ripe for someone escalating their permissions. If I had access to the file the SSIS package is looking for, I can add a drop database, create a new user and give them SA rights, etc - anything the account that runs the SSIS package can do, a nefarious person could exploit.

Roll your own approach

If you're adamant about reading the file yourself, add two Variables to your SSIS package and supply values like the following

  • User::QueryPath -> String -> C:\path\to\file.sql
  • User::QueryActual -> String -> SELECT 1;

Add a Script Task to the package. Specify as a ReadOnly variable User::QueryPath and specify as a ReadWrite variable User::QueryActual

Within the Main you'd need code like the following

string filePath = this.Dts.Variables["User::QueryPath"].Value.ToString();
this.Dts.Variables["User::QueryActual"].Value = System.IO.File.ReadAllText(filePath);

The meat of the matter is System.IO.File.ReadAllText. Note that this doesn't handle checking whether the file exists, you have permission to access, etc. It's just a barebones read of a file (and also open to the same injection challenges as the above method - just this way you own maintaining it versus the fine engineers at Microsoft)

billinkc
  • 59,250
  • 9
  • 102
  • 159
0

You can build your query by using both Variable and Parameter.

For example:

Parameter A: dbo

Build your variable A (string type) as : "Select * FROM server.DB." + ParameterA + ".Table"

So if you need to change the schema, just change the parameter A will give you the corresponding query in variable A.

LONG
  • 4,490
  • 2
  • 17
  • 35