SSIS Tip 1: Using Select Query to define source

Introduction

This tip is continuation with my article posted here. In this article i will demonstrate to use SQL query to create initial OLEDB source, Instead of direct table access. 

Using the code 

Please have look on article mentioned in the Point of Interest section, this will certainly going to help you. Now if you select direct table in SSIS project, you code like this :- 

// Set the custom properties of the source. 
 srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View 
 srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SourceTable]"); 

Now for changing code for inclusion of the  SQL query 

string formatSQL = "SELECT * from [dbo].[SourceTable] where [Age] < 31";
// Set the custom properties of the source. 
 srcDesignTime.SetComponentProperty("AccessMode", 2); // Mode 2 : OpenRowset / SQL Command 
 srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SourceTable]"); 
 srcDesignTime.SetComponentProperty("SqlCommand", formatSQL);

Here  we have done these changes

• AccessMode is changed from 0 to 2, i.e. to accommodate the SQL Command
• New property "sqlcommand" added , which will get actual SQL query, 

Once coded you can check same in the OLEDB source connection here 

Points of Interest 

Have a look at the other article of series  

• SSIS: By coding
• SSIS: By coding Part 2

History

Check out more article in this series!