Dynamic SQL in SQL Server Integration Services (SSIS)
I sometimes miss the Dynamic Properties Task in DTS. I used it for all sorts of functionality - and I'm struggling to replace some of that flexibility in complex SSIS development.
I recently developed a package with dynamic SQL serving as the data source for a Data Flow. I accepted the mission and here's an example to demonstrate how I accomplished this:
Drag a Data Flow Task onto the Control Flow canvas:
Double-click the Data Flow task to edit it. Drag a DataReader Source onto the Data Flow canvas and double-click it for editing. The Advanced Editor opens. Select or create an ADO.Net connection manager on the Connection Managers tab:
On the Component Properties tab, enter an SQL Statement in the SqlCommand property (I query a table I created named dbo.TestTable which contains three columns: TestID [Identity PK], TestName [VarChar(20)], and TestValue [int]):
Verify the SqlCommand field names on the Column Mappings tab:
Drag a Flat File Destination onto the Data Flow canvas and (this is important) connect the output of the DataReader Source to the Flat File Destination with a Data Path (green arrow) [Note: Previously, I incorrectly identified this as a Precedence Constraint. Precedence Constraints are found only on the Control Flow. Data Paths are the connecting arrows on the Data Flow. My apologies for any readers misled.]:
Double-click the Flat File Destination to edit it. On the Connection Manager page, click the New button to create a new Flat File Connection Manager:
Assign the Flat File Connection Manager a name (I used "OutFile") on the General page. Enter a filename ("OutFile.txt") and specifics regarding collation and row delimiters:
Click Columns to configure column details.
Why? I always create Flat File Connection Managers for new files from the Flat File Destination task. If the target file does not exist, the Destination task has no better way to know which columns to expect.
Note the Preview grid displays the expected columns (read from the DataReader Source via the precendence constraint connector):
Click OK to create the Connection Manager and return to the Flat File Destination Editor:
Click the Mappings item in the page list to view (and auto-map) the DataReader Source columns to the Flat File Destination columns:
Return to the Control Flow tab and click any empty space on the Control Flow canvas.
Why? You are about to create a new Variable. Unlike DTS variables (which were all global in scope), SSIS variables have specific scope. Scope is determined, for better or worse, by the Task or SSIS Object that has focus when you create a new variable. I say "for better or worse" because I've not yet discovered a method for changing variable scope during the variable creation process. Clicking on the Control Flow canvas gives it focus, and Package-scoped (global) variables may then be created.
Click View, then Other Windows, then Variables to display the Variables dialog. Set the Data Type to String and enter some query in the Value field. Note: the query used here must expose the same columns as the query in the SqlCommand property of the DataReader Source or the Data Flow will fail during pre-execution package validation (There is most likely a method to address this - more later, perhaps...). I created a variable named SQLOut with a default value of "SELECT 0 AS TestID, 'Zero' AS TestName, 0 AS TestValue":
Drag a Script Task onto the Data Flow canvas. Connect it via Data Path (green arrow) [Note: Previously, I incorrectly identified this as a Precedence Constraint. Precedence Constraints are found only on the Control Flow. Data Paths are the connecting arrows on the Data Flow. My apologies for any readers misled.] to the Data Flow task as shown:
Double-click the Script Task to open the Script Task Editor. Click on Script in the page list and add the variable name created earlier (SQLOut) to the ReadWriteVariables property:
Click the Design Script button to open the Visual Studio for Applications (VSA) Script Editor. Enter the following script in the Main subroutine of the ScriptMain class:
MsgBox(Dts.Variables("SQLOut").Value)
Dts.Variables("SQLOut").Value = "SELECT 1 AS TestID, 'Unos' AS TestName, 12 AS TestValue"
MsgBox(Dts.Variables("SQLOut").Value)
Dts_TaskResult = Dts_Results_Success
To test, close the VSA Editor and click the OK button to close the Script Task Editor. Right-click the Script Task and click Execute Task:
The first MsgBox command should execute displaying the default contents of the SQLOut variable:
Click the OK button. The second MsgBox command should display the updated SQLOut variable contents:
Click the OK button. The Script task should complete without error:
