Learn SSIS developement

marrowyung
marrowyung used Ask the Experts™
on
hi,

For MS SQL SSIS, if I want to be a SSIS developer what should I learn and what language usually it use ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
SSIS is a tool to be used. For Script Task (one of the tasks available in SSIS), you can write C# codes accordingly.
marrowyungSenior Technical architecture (Data)

Author

Commented:
oh, as you know a lot of SSIS developer job on the street and I don't know what we have to develop and what language it use. so only C# ?

UI drag and drop can't do a lot of thing ?
Ryan ChongSoftware Team Lead

Commented:
what language it use. so only C# ?

you can run VB.NET or C#

UI drag and drop can't do a lot of thing ?

actually SSIS comes with a lot of features and stuffs you can testing with.

SQL Server Integration Services
https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver15

Script Task
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/script-task?view=sql-server-ver15
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

marrowyungSenior Technical architecture (Data)

Author

Commented:
"actually SSIS comes with a lot of features and stuffs you can testing with."

I knew what you meat, but what i mean is , what usually a SSIS developer will need to develop other than drag and drop SSIS task boxes.

why still need code?

any learning resource for all level?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> what usually a SSIS developer will need to develop other than drag and drop SSIS task boxes.

Yes, that's partially correct..
Even though Drag and Drop SSIS Task boxes might help most of the times, if you have some basic expertise in SQL Server TSQL programming, it would be good enough as few tasks have less performance compared to the default SSIS Tasks.
In addition to that, you might need to do some Script tasks in C# or VB.Net to cater the features not available in SSIS by default..

>> any learning resource for all level?
This can be good start with step by step tutorial for all tasks
http://docshare04.docshare.tips/files/23226/232267206.pdf
Ryan ChongSoftware Team Lead

Commented:
why still need code?

Well, probably the reason being Microsoft don't want to limit the SSIS itself, by adapting to .NET programming, which is a very powerful feature, it can expand the possibilities of system integration, which covers up the gaps that could happen when just use drag and drop features.
marrowyungSenior Technical architecture (Data)

Author

Commented:
" by adapting to .NET programming, which is a very powerful feature"

so seems has to learn .net programming, C# and VBA all together?  any one language can do all for SSIS ?

or just T-SQL is ok ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Raja Jegan R,

" if you have some basic expertise in SQL Server TSQL programming, it would be good enough as few tasks have less performance compared to the default SSIS Tasks"

 so that mean T-SQL, c# script can't do better than default SSIS task ?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> so that mean T-SQL, c# script can't do better than default SSIS task ?

Sorry, my bad... should have explained more correctly earlier..
Built in SSIS Drag and Drop tasks can help accomplish most of your tasks in SSIS however few things would be optimal in
1. Execute T-SQL Task(with T-SQL Scripts) compared to built in SSIS tasks.
2. Script component(with C# or VB.Net) would be faster compared to built in tasks.
3. Specific project requirements might not be accomplished using built in tasks and hence you need to use third party plugins(paid) or Script component(free to develop by ourselves) to accomplish those..

Hope it makes things clearer now..
marrowyungSenior Technical architecture (Data)

Author

Commented:
more clear now .

BTW, which one is common use ? 1,2 or 3 ?

"hird party plugins(paid) or Script component(free to develop by ourselves) to accomplish those..
"

where can I learn this and where can I download it  ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Raja Jegan R,

"This can be good start with step by step tutorial for all tasks
http://docshare04.docshare.tips/files/23226/232267206.pdf"

seems it focus on using drag and drop of SSIS but not programming, right?

any source for development side?  this one : https://www.amazon.com/-/zh_TW/dp/B07Q55SYCT/ref=sr_1_2?keywords=SQL+Server+SSIS+develpement&qid=1576744929&sr=8-2-spell?
Ryan ChongSoftware Team Lead

Commented:
so seems has to learn .net programming, C# and VBA all together?  any one language can do all for SSIS ?

or just T-SQL is ok ?

To use Script Task, the minimal requirement is to know either VB.NET (not VBA) or C#

By integrating with script task, you can use the preferred .NET language (VB.NET or C#) to call other programs or scripts when it's necessary. While T-SQL is needed for you to tackle another angle of issues, such as to extract/ transform your data. So, we can say that all are important elements to be learned in order to construct a complete SSIS solutions.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> seems it focus on using drag and drop of SSIS but not programming, right?

Yes, that's correct..
It would be a good start to first know what SSIS by default offers so that we can try to learn or concentrate more on the additional scripting or programming required to handle those odd scenarios..

For the development, you can try any C# or VB.Net Programming books..
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
I have a couple of articles that are not directly related to SSIS training but will help you along .. SQL Server Training and SSIS Interview Questions.

SSIS is a niche product and there just aren't many training resources available, and definately not in schools.  Also I have yet to see any training available on the VB.NET scripting and C#.NET scripting that SSIS uses.  I learned it out of a book when my employer did a SQL Server 2000 to 2005 migration and needed a guy to convert DTS packages to SSIS.

Add to the above Microsoft isn't putting a lot of effort into improving SSIS, which has ripples in that there's not many real good training options.  Back in 2015 I asked a Microsoft engineer why the most recent version of SSDT (back then) only included 'rounded corners', and his joking response was 'Because XBox made too much money'.

By far the best free SSIS training I've seen is Pragmatic Works online videos.  There are other videos on YouTube but in my experience the quality is poor.

Good luck.
Jim
marrowyungSenior Technical architecture (Data)

Author

Commented:
Jim Horn,

you are SQL Server Data Dude, so you are more on coding side on SQL server, right?

" I learned it out of a book when my employer did a SQL Server 2000 to 2005 migration and needed a guy to convert DTS packages to SSIS. "

what books your recommend ?  this one : https://www.amazon.com/-/zh_TW/dp/B07Q55SYCT/ref=sr_1_2keywords=SQL+Server+SSIS+develpement&qid=1576744929&sr=8-2-spell ?

" his joking response was 'Because XBox made too much money'. "

make sense ... ~! hahhaaa

"By far the best free SSIS training I've seen is Pragmatic Works online videos."
tks.

this is a good one ! save time on buying books.

but which good SSIS book is from intermediate to expert assume that one I read the video from pragmatic works online I can be an intermediate and start going to be an experts.

Raja Jegan R,

"For the development, you can try any C# or VB.Net Programming books.."

for SSIS and later on SSAS development, if I go from SSIS to SSAS , which programming language is better?

any book on coaching C# or VB.net ONLY on SSIS ? Pragmatic Works online videos can do it too ? bring me from beginner to intermediate
?

Ryan Chong and Raja Jegan R,

any good video training for me to easy jump start to C# and ONLY focus on SSIS programming ?

like the one Jim Horn suggest is good on SSIS learning.

Ryan,

"While T-SQL is needed for you to tackle another angle of issues, such as to extract/ transform your data. So, we can say that all are important elements to be learned in order to construct a complete SSIS solutions."

or it seems T-SQL and C# is already very necessary ?
Ryan ChongSoftware Team Lead

Commented:
any good video training for me to easy jump start to C# and ONLY focus on SSIS programming ?

emmm, not really, but just to think that you actually can write a normal C# program within SSIS, it's pretty much the same experience when you coding C# in VS.

or it seems T-SQL and C# is already very necessary ?

in the context with SSIS, yeah, I would think both are important and necessary to know.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"emmm, not really, but just to think that you actually can write a normal C# program within SSIS, it's pretty much the same experience when you coding C# in VS."

I just talk to some developer here and it seems VB.net is better for this job as C# usually good for UI design ?

as I have no knowledge on VS programming on C# and Vb.net, so I need some resource.

"in the context with SSIS, yeah, I would think both are important and necessary to know."

or T-SQL and VB.net ?
Ryan ChongSoftware Team Lead
Commented:
as I have no knowledge on VS programming on C# and Vb.net, so I need some resource.
A lot of resources available online, but you can always rely on MSDN for documentation and google online for the examples.

or T-SQL and VB.net ?

both and to know VB.NET if you need to write codes via Script Task
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I just talk to some developer here and it seems VB.net is better for this job as C# usually good for UI design ?
Is this a question?  Experts here do not have access to 'some developer' and 'this job', so it seems like we've moved beyond the original question to the extent that experts here can answer it.

Also it would be VASTLY better to state your business needs and then experts can comment on design solutions, as opposed to 'this technology is better than that' without experts here having any context to make that decision.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> for SSIS and later on SSAS development, if I go from SSIS to SSAS , which programming language is better?

To be clear, For SSIS you might require coding on C# or VB.Net.
However, for SSAS its a different arena involving Cubes and Dimensions and if you wish to do some programming, then MDX, DAX and DMX are the languages to be learnt..

>> any book on coaching C# or VB.net ONLY on SSIS ? Pragmatic Works online videos can do it too ? bring me from beginner to intermediate
?

My recommendation to master in SSIS is by first going through the individual components that are built in SSIS by going through the reference material I've already shared out.. (sharing again below)
http://docshare04.docshare.tips/files/23226/232267206.pdf
Once familiar and you started doing SSIS related activities, then you might encounter scenarios wherein C# or VB.Net coding is required and then you can start focusing on learning those..
The reason I suggest this approach is that a beginner can achieve most of the tasks with the Built in drag and drop tasks itself..
Just let me know if you think otherwise..
marrowyungSenior Technical architecture (Data)

Author

Commented:
Raja Jegan R,

"My recommendation to master in SSIS is by first going through the individual components that are built in SSIS by going through the reference material I've already shared out.. (sharing again below)
http://docshare04.docshare.tips/files/23226/232267206.pdf"

sure.

"Once familiar and you started doing SSIS related activities, then you might encounter scenarios wherein C# or VB.Net coding is required and then you can start focusing on learning those.."

this is what I am asking, which language to use. As I am not a developer but SQL DBA, I think focus in one language is better than 2 x language, if nearly do the same thing in SSIS domain.

tks all.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> As I am not a developer but SQL DBA, I think focus in one language is better than 2 x language, if nearly do the same thing in SSIS domain.

To be frank, I've been doing SSIS development for a long time and haven't faced a need to do Scripting tasks so far..
While checked with few of my developers, they prefer C# over VB.Net. If you will be using Excel, then you can focus on VB.net as it can help you develop Macros on Excel.
marrowyungSenior Technical architecture (Data)

Author

Commented:
" If you will be using Excel, then you can focus on VB.net as it can help you develop Macros on Excel"

exactly !

"To be frank, I've been doing SSIS development for a long time and haven't faced a need to do Scripting tasks so far.."

so you just drap and drop SSIS box  and finish everything ? you are the leader of that SSIS team ?

"While checked with few of my developers, they prefer C# over VB.Net"

why then ? what is the advantage of C# on SSIS development ?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> so you just drap and drop SSIS box  and finish everything ? you are the leader of that SSIS team ?

Yes, it is under my team..

>> what is the advantage of C# on SSIS development ?

Its just a personal preference and can vary across developers..
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Yes, it is under my team..
"

so is that means your team member do much complex SSIS task than you ?

"Its just a personal preference and can vary across developers.."

ok.
marrowyungSenior Technical architecture (Data)

Author

Commented:
and also from time to time we heard about ELT and this link also refer to it:

https://blog.panoply.io/announcing-panoply-availability-on-microsoft-azure?utm_source=hs_email&utm_medium=email&utm_content=79572173&_hsenc=p2ANqtz-9yPv0YF17Mj8thi6-CwGA0JM9sReVuJvdTncfK3SQ01R0N_F9CBzyqg7-eEi8Y0saGN6Cg1KOOWJ6XBSl1i5zIbTNu1Q&_hsmi=79572866

so is it learning ETL is useless right now but ELT ?  MS SQL server SSIS seems is only for ETL but not ELT ?

so we have to use tools like panoply with SQL Azure to make use of ELT on MS SQL ?

any resource to learn ELT ? ELT in deep development also need programming ?
Ryan ChongSoftware Team Lead

Commented:
ETL = Extract , Transform and Load

basically we use it to clean / transform your original dataset to another form which can be used.

so it's a method that we are using. ETL Tool would be the tool to be used to conduct ETL process.

there are many ETL tools can be used. I have developed different kind of ETL processes before. It could be a very simple one with just pure SQL scripts (of course, you can include SQL validations as well), or sometimes, we need the whole process to integrate with different kind of tools. You can say SSIS is one of the tools we can used and treated it as an ETL Tool.

I didn't use Panoply before, it could be just one of the options available. so you need evaluate whether it's suitable in your scenario.
marrowyungSenior Technical architecture (Data)

Author

Commented:
so you mean is , ETL and ELT is not the SAME thing and ELT is not going to replace ETL ?

what I understand this, ELT is claim to be much better process than ETL as discussed in the link post.

how about ELT tools, any ?
Ryan ChongSoftware Team Lead

Commented:
sorry, I don't really know for "ELT". Is it a typo of "ETL" ?

and I have corrected my previous comment:

ETL = Extract , Transform and Load
(my original meaning refer to traditional ETL)

I didn't know much about tools using ELT (extract, load, transform) methodologies, so can't provide you much comments on it, but Microsoft these days are pushing a lot on Power BI, so probably it's a ELT (and / and not a ETL) tool. I tried Power BI before in which it can load data, and then transform it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"sorry, I don't really know for "ELT". Is it a typo of "ETL" ?

ELT is a new one ! Extract load transform ,which claim to be a faster ETL method, in which data already loaded in the repository before transform, seems much less code.
Ryan ChongSoftware Team Lead

Commented:
I would think it is on case by case basis to determine which approaches to be used, whether to use ETL or ELT method.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial