In those scenarios it's significantly faster, even with the manual element, to pump the data into a stage table using import/export wizard and then write sql to go from staging into the app.įor other more permanent solutions I've implemented python + sqlalchemy + pandas, which are seriously powerful libraries and have no associated licensing costs. If they're just going to load data from this source 3 times and abandon it there's no sense in spending 2 weeks formalizing it and building in robust features like error handling. My role supports client facing consulting teams that switch hands constantly, have no clear standard process definitions, and basically are subject to the clients' whim on all things, it's a serious risk/reward balance when building data flows. My time is precious to me because there's so much to handle. I work for an ops team where there's no clear role definition anymore, I do azure infra management for our sites, dba for the site's backend, data development & analysis, and decision support/BI dev. The other argument here is if you're working for a team that doesn't need to hand off process, or process is unstable. Since learning SSIS was just as difficult as the other ones because i barely had on the job experience with it, i guess that leveled the playing field in terms of preference but i haven't done one of each long enough to see which is more beneficial for what or which i prefer doing.Ĭonceptually the SSIS was a bit easier because the head guy basically drew a process flow diagram with boxes signifying data tables, sources, destinations with lines pointing to each showing that a sql is executed here, then a job procedure and i basically replicated that flow/diagram on SSIS gui. Once i maintained all those tools and improved some of them they were like hey, try figuring this out and told me to take a monthly data import, query, job procedure and to just automate the entire thing with SSIS. So basically i was thrown in with a clean slate with tools developed with cobol, vba, vbs, etc, and databases using everything from ms access, microsoft sql server, oracle, etc. I'm fairly out of school so when i went to work at X they have all sorts of shit to do the same thing depending on when a person was hired and their preference in language to developing a tool. But why would you reinvent the wheel unless you absolutely needed to?Īppreciate the answer. You could use Powershell to duplicate everything in SSIS. There’s no reason you couldn’t invoke Powershell from an SSIS package using a run process task or use Powershell to kick off a package. You can use Powershell to do ETL, but it’s a powerful scripting language, you can use it for just about anything. Comparing Powershell to SSIS is comparing apples and crates. Like with any tool, you have to know quirks and tricks for performance and scalability, but for 80-90% of use cases it does the job fairly well. Since Microsoft put a whole ecosystem around it, it makes it very easy to setup a whole ETL system from development to deployment with automation without a ton of learning curve. Where it shines is time to deployment, orchestration and centralization. In terms doing an actual ETL, I don’t find SSIS any worse or better than any other tool, especially if you understand what it’s doing under the covers. I recently acquired another job at a Law Firm and they're doing it the powershell way, and the fast food corp seems to want to move the SSIS route. So my question is, is either or more beneficial? Does it have to do with how big the data is? Do a lot of you hate SSIS because you're already so good at other methods that ssis seems useless? I asked why? and he just gave me this convoluted response that didn't answer my question. I asked the old school guy of the department why they wanted me to do this and he said because they're trying to move away from using powershell to do this. But after 2 days of just clicking around and reading forums i was able to complete the package. They just said here's where the data source comes in, at this time/date, the job procedure should be this, and here's the destination - Make it happen.Īt first i was like oh shit, this looks daunting i'll never be able to do this. Mind you at this point i was completely new to SSIS but i had a bit of visual studio/C# experience only from school. I worked for huge fast food corp and i was just in charge of maintaining some of their old tools (a lot of oldschool shit written in vba) and after a few weeks they just gave me this project to do (i guess to see if i'd sink or swim). I read another thread here where a lot of you guys hated SSIS. Hey guys so this is really just to start a discussion on the pros and cons of either or.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |