SSIS custom tool to run SQL query using c#

Question

I am fairly new to SSIS and I am very interested in creating custom data transformation components using c#.  So I was wondering if it’s not too much trouble how I could create a tool that connects to a SQL Server database and performs a simple query. Obviously I could do this using tools that are already in the BIDS, but I really want to know more about creating custom components.   I am using Visual Studio 2008.

Let’s say I have a server in SSMS titled ‘ServerABC’ and within that server I have a table titled ‘Employee_Details’ within a database titled ‘Company_Data’.

The table ‘Employee_Details’ has the following columns:

Employee_Number, Employee_Sex,  Employee_Age_Bracket, Employee_Position, and   Employee_Fired.

 

How then, using c#, could a create a custom tool that runs the following SQL query:

 

SELECT
 EMPLOYEE_NUMBER AS
 EMPLOYEE_NUMBER,

CASE 
WHEN
 EMPLOYEE_FIRED = 'TRUE
' THEN
 'FIRED'
WHEN 
EMPLOYEE_FIRED = 'FALSE
' THEN
 EMPLOYEE_SEX
END 
AS
 EMPLOYEE_SEX

FROM 
EMPLOYEE_DETAILS

 

 

What I would like to be able to do then is add some property to the tool that I can configure that determines the column in bold above. So if in this property field (we can just call it ‘Examine Column’) if I type in [Employee_Sex] it would run the above query. If I typed ‘Employee_Age_Bracket’ into this field the tool would run the following query would be run:

 

SELECT
 EMPLOYEE_NUMBER AS
 EMPLOYEE_NUMBER,



CASE 
WHEN
 EMPLOYEE_FIRED = 'TRUE
' THEN
 'FIRED'

WHEN 
EMPLOYEE_FIRED = 'FALSE
' THEN
 Employee_Age_Bracket

END 
AS
 Employee_Age_Bracket




FROM Employee_Details

 

 

I would like to also be able to select a server, database, and table (even if it’s using a connection manager) in the same fashion. The reason I just don't want to use a different tool to run the query is that I want to learn how to create custom tools and I feel this is a pretty simple  (hopefully) place to start.

Any help would be greatly appreciated.

Thanks : )

Answer 1

I hope this can be acheived with the help of the SCRIPT tasks available in the SSIS control flow. In SSIS 2008 script tasks codes are written in c#. With the help of this you can achieve writing queries and building code blocks inside script tasks to cater your requirement.

---

Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Answer 2

Hi,
I'm a bit confused. What is the output of this "tool"? Do you want it simply to execute a query and then return some results to you (if so, why are you posting on a SSIS forum)?
Or, do you want a tool that will provide you with a .dtsx package based on some parameters that you supply to it?
Or, something else?

-Jamie

---

http://sqlblog.com/blogs/jamie_thomson/ | http://jamiethomson.spaces.live.com/ | @jamiet