February 19, 2015 at 10:49 am
Tell me about your experience as a SSIS developer?
I answered-SSIS is nothing but an ETL tool where we extract data from different heterogenous sources like flatfiles,excel,csv etc and load them into SQL server in a required format so it becomes the central repository for analysis process.
Can you share some of your answers besides this one--
February 19, 2015 at 11:34 am
The correction I would make is that the sources that you mention aren't what I'd call "heterogeneous". The sources that you mention are very specific and, although the basic structure of the individual files will be different even in the same class of structures, the classes are each basically handled in just a couple of different ways.
I also wouldn't call SSIS just and ETL tool. It's also a process control tool that can be used to control processes whether or not ETL is one of those processes.
Shifting gears a bit, I don't use SSIS. For all those things you've mentioned, I've found it to be faster and easier to do things using T-SQL and the occasional trip through xp_CmdShell to the operating system (DOS commands and sometimes batch files, as well) or the occasional use of PoSH and/or WMI. For example, I've written two separate world wide call accounting packages that do anything and everything from FTP download to modem downloads from "PollCats", etc, to uploading to 3rd party processing ("Atlantax" and "ZPDI", for example) and everything in between without going anywhere near SSIS.
None of that should be interpreted as me thinking that SSIS is bad. I just don't need to use it for what most people would use it for and have personally elected to avoid it so that I don't have yet another "system" to maintain and keep secure.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2015 at 12:09 pm
abhisecks (2/19/2015)
Tell me about your experience as a SSIS developer?I answered-SSIS is nothing but an ETL tool ...
WRONG!
SISS can do a lot more than just ETL!
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 19, 2015 at 12:57 pm
Alvin Ramard (2/19/2015)
abhisecks (2/19/2015)
Tell me about your experience as a SSIS developer?I answered-SSIS is nothing but an ETL tool ...
WRONG!
SISS can do a lot more than just ETL!
No disagreement here but care to elaborate for the general population?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2015 at 9:49 pm
SQL Server Integration Services (SSIS) is a platform for building high performance data integration and workflow solutions. It allows creation of packages or SSIS packages which are made up of tasks that can move data from source to destination and alter it if required. SSIS is basically an ETL (Extraction, Transformation, and Load) tool whose main purpose is to do extraction, transformation and loading of data but it can be used for several other purposes, for example, to automate maintenance of SQL Server databases, update multidimensional cube data or send e-mails detailing the status of the operation as defined by the user. SSIS is a component of SQL Server 2005/2008 and is the successor of DTS (Data Transformation Services) which had been in SQL Server 7.0/2000
Typical Use of Integration Services:
- Merging Data from Heterogeneous Data Stores
- Populating Data Warehouses and Data Marts
- Cleaning and Standardizing Data
- Building Business Intelligence into a Data Transformation Process
- Automating Administrative Functions and Data Loading
____________________________________________________________
APFebruary 25, 2015 at 8:32 am
I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".
February 25, 2015 at 8:36 am
anshulparmar (2/19/2015)
SSIS is a component of SQL Server 2005/2008 ...
It's also available in SQL Server 2012 and 2014 and my guess is that next versions of SQL Server will have SSIS as well... 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 25, 2015 at 8:52 am
Moin Ul Haque (2/25/2015)
I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".
Just curious... what happens to backups on other machines if the machine this SSIS package is on is down/offline?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2015 at 12:42 pm
Jeff Moden (2/25/2015)
Moin Ul Haque (2/25/2015)
I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".Just curious... what happens to backups on other machines if the machine this SSIS package is on is down/offline?
I missunderstood your question initially 🙂 The backups are copied over to a shared backup server and deleted from the servers' local drives. Unfortunately there's no failover mechanism currently in place if the SSIS machine itsellf is down. Luckily we have SCOM monitoring on the admin servers and I get alerts on my phone if it goes down so it kinda works well in my scenario. Besides if the admin servers are down, then I have more important things to worry about than just backups to be honest 🙂 I will certainly think about adding some redundancy around this. Thanks for pointing it out.
February 26, 2015 at 5:00 pm
Moin Ul Haque (2/26/2015)
Jeff Moden (2/25/2015)
Moin Ul Haque (2/25/2015)
I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".Just curious... what happens to backups on other machines if the machine this SSIS package is on is down/offline?
I missunderstood your question initially 🙂 The backups are copied over to a shared backup server and deleted from the servers' local drives. Unfortunately there's no failover mechanism currently in place if the SSIS machine itsellf is down. Luckily we have SCOM monitoring on the admin servers and I get alerts on my phone if it goes down so it kinda works well in my scenario. Besides if the admin servers are down, then I have more important things to worry about than just backups to be honest 🙂 I will certainly think about adding some redundancy around this. Thanks for pointing it out.
Thanks for the feedback. I'd seriously worry about the backups on the other machines if the SSIS machine is down because if no PIT backups are being taken, all of the logfiles are going to grow well beyond what you want them and then you'll have to fix them all. You also stand a chance of the ones "on the fringe" of actually running out of disk space depending (of course) on what they do and how much extra room you have on the log file drive(s).
That's why I won't rely on any such "central management" for backups even if it's thoughtfully designed. I'll admit that I also don't have 100s of servers but, if I did, I'd find a way to easily make each of them autonomous for backups.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2015 at 6:33 pm
Jeff Moden (2/26/2015)
Moin Ul Haque (2/26/2015)
Jeff Moden (2/25/2015)
Moin Ul Haque (2/25/2015)
I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".Just curious... what happens to backups on other machines if the machine this SSIS package is on is down/offline?
I missunderstood your question initially 🙂 The backups are copied over to a shared backup server and deleted from the servers' local drives. Unfortunately there's no failover mechanism currently in place if the SSIS machine itsellf is down. Luckily we have SCOM monitoring on the admin servers and I get alerts on my phone if it goes down so it kinda works well in my scenario. Besides if the admin servers are down, then I have more important things to worry about than just backups to be honest 🙂 I will certainly think about adding some redundancy around this. Thanks for pointing it out.
Thanks for the feedback. I'd seriously worry about the backups on the other machines if the SSIS machine is down because if no PIT backups are being taken, all of the logfiles are going to grow well beyond what you want them and then you'll have to fix them all. You also stand a chance of the ones "on the fringe" of actually running out of disk space depending (of course) on what they do and how much extra room you have on the log file drive(s).
That's why I won't rely on any such "central management" for backups even if it's thoughtfully designed. I'll admit that I also don't have 100s of servers but, if I did, I'd find a way to easily make each of them autonomous for backups.
Just to be clear, there are HA options for SSIS servers to prevent this problems. Although, there's no perfect solution for anything.
March 11, 2015 at 7:46 pm
Luis Cazares (2/26/2015)
Jeff Moden (2/26/2015)
Moin Ul Haque (2/26/2015)
Jeff Moden (2/25/2015)
Moin Ul Haque (2/25/2015)
I can quote my own experience here - Out of need, I was asked to develop a database maintenance solution which would monitor several things on our SQL server infrastructure e.g. free drive space, index fragmentation and recommendation, manage backups, archive data and some other tasks. Since we didn't want to spend loads of money on off the shelf solutions, i built the whole thing in SSIS. It is configurable so you can add servers to the "config" database and it will perform the configured maintenance on all target servers. It used PowerShell scripts and standard SSIS components. In all honestly I would say that SSIS solution was FAR from "just an ETL".Just curious... what happens to backups on other machines if the machine this SSIS package is on is down/offline?
I missunderstood your question initially 🙂 The backups are copied over to a shared backup server and deleted from the servers' local drives. Unfortunately there's no failover mechanism currently in place if the SSIS machine itsellf is down. Luckily we have SCOM monitoring on the admin servers and I get alerts on my phone if it goes down so it kinda works well in my scenario. Besides if the admin servers are down, then I have more important things to worry about than just backups to be honest 🙂 I will certainly think about adding some redundancy around this. Thanks for pointing it out.
Thanks for the feedback. I'd seriously worry about the backups on the other machines if the SSIS machine is down because if no PIT backups are being taken, all of the logfiles are going to grow well beyond what you want them and then you'll have to fix them all. You also stand a chance of the ones "on the fringe" of actually running out of disk space depending (of course) on what they do and how much extra room you have on the log file drive(s).
That's why I won't rely on any such "central management" for backups even if it's thoughtfully designed. I'll admit that I also don't have 100s of servers but, if I did, I'd find a way to easily make each of them autonomous for backups.
Just to be clear, there are HA options for SSIS servers to prevent this problems. Although, there's no perfect solution for anything.
Exactly why I don't use SSIS for backups. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2015 at 7:56 pm
SSIS is my brain if I could pre-write all of my actions that I wanted to print in a notebook and have that notebook execute those actions automatically over time. Those actions go beyond just extracting, transforming and loading data.
For example:
1 - Wake up
2 - Look in the mirror.
3 - Decide if to brush my hair or scream first.
4 - Use the bathroom.
5 - Ensure I didn't miss.
6 - Brush my teeth.
7 - Floss.
8 - Use mouthwash.
And so forth. It's me in a box without actually being in a box. 😎
P.S
I don't actually need SSIS to do all of this either. But, that's another discussion.
March 12, 2015 at 7:45 am
Jeff Moden (2/19/2015)
The correction I would make is that the sources that you mention aren't what I'd call "heterogeneous". The sources that you mention are very specific and, although the basic structure of the individual files will be different even in the same class of structures, the classes are each basically handled in just a couple of different ways.I also wouldn't call SSIS just and ETL tool. It's also a process control tool that can be used to control processes whether or not ETL is one of those processes.
Shifting gears a bit, I don't use SSIS. For all those things you've mentioned, I've found it to be faster and easier to do things using T-SQL and the occasional trip through xp_CmdShell to the operating system (DOS commands and sometimes batch files, as well) or the occasional use of PoSH and/or WMI. For example, I've written two separate world wide call accounting packages that do anything and everything from FTP download to modem downloads from "PollCats", etc, to uploading to 3rd party processing ("Atlantax" and "ZPDI", for example) and everything in between without going anywhere near SSIS.
None of that should be interpreted as me thinking that SSIS is bad. I just don't need to use it for what most people would use it for and have personally elected to avoid it so that I don't have yet another "system" to maintain and keep secure.
I'm not a fan of SSIS 2012 for sure, 2008 is OK. But I like our idea Jeff of not using it at all, but some of us have no choice, if corporate says to use it that's what we got to do.
🙁
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 12, 2015 at 7:59 am
below86 (3/12/2015)
I'm not a fan of SSIS 2012 for sure, 2008 is OK.🙁
What's worse in 2012 than in 2008?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply