dynamic change of flat file mappings
-
John Rowan (8/10/2009)
It may just mean that my definition of 'easy' differs from yours. My post previous to your request that I try your product and provide you with feedback provided 2 separate solutions to the problem, both of which I would consider easy.Would I use bcp for this, probably not. Would I go to my boss and tell him that I can't do this without purchasing a 3rd party component, definatly not!
Personally, I would go with a solution where each data flow for each file type is managed in an individual SSIS pacakge and have a master package read through the directory and call the appropriate child component. This isolates each data flow and allows for ease of maintenance, data flow customization based on file type, and scalability.
1. Your first solution requires the user to be programmer. This automatically makes this solution hard for most of the DBAs out there. And we are even not talking about the limited transformation capabilities of the bcp.
2. Your second solution is reasonable, when talking about 5-10 transformation packages. If you have to maintain more packages, then this solution becomes both tedious and error prone.
Do you agree with these statements John?
p.s.
Everything is possible to accomplish without use of third-party components. But I will ask the same question I have asked many times before. Is it worth it and what is the cost of avoiding it? Most of the companies doesn't have unlimited resources to throw after reinventing the wheel. If there is easier solution, why not use it and save the money for the custom business logic implementation?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
CozyRoc (8/10/2009)
John Rowan (8/10/2009)
It may just mean that my definition of 'easy' differs from yours. My post previous to your request that I try your product and provide you with feedback provided 2 separate solutions to the problem, both of which I would consider easy.Would I use bcp for this, probably not. Would I go to my boss and tell him that I can't do this without purchasing a 3rd party component, definatly not!
Personally, I would go with a solution where each data flow for each file type is managed in an individual SSIS pacakge and have a master package read through the directory and call the appropriate child component. This isolates each data flow and allows for ease of maintenance, data flow customization based on file type, and scalability.
1. Your first solution requires the user to be programmer. This automatically makes this solution hard for most of the DBAs out there. And we are even not talking about the limited transformation capabilities of the bcp.
2. Your second solution is reasonable, when talking about 5-10 transformation packages. If you have to maintain more packages, then this solution becomes both tedious and error prone.
Do you agree with these statements John?
p.s.
Everything is possible to accomplish without use of third-party components. But I will ask the same question I have asked many times before. Is it worth it and what is the cost of avoiding it? Most of the companies doesn't have unlimited resources to throw after reinventing the wheel. If there is easier solution, why not use it and save the money for the custom business logic implementation?
-
CozyRoc (8/10/2009)
John Rowan (8/10/2009)
It may just mean that my definition of 'easy' differs from yours. My post previous to your request that I try your product and provide you with feedback provided 2 separate solutions to the problem, both of which I would consider easy.Would I use bcp for this, probably not. Would I go to my boss and tell him that I can't do this without purchasing a 3rd party component, definatly not!
Personally, I would go with a solution where each data flow for each file type is managed in an individual SSIS pacakge and have a master package read through the directory and call the appropriate child component. This isolates each data flow and allows for ease of maintenance, data flow customization based on file type, and scalability.
1. Your first solution requires the user to be programmer. This automatically makes this solution hard for most of the DBAs out there. And we are even not talking about the limited transformation capabilities of the bcp.
2. Your second solution is reasonable, when talking about 5-10 transformation packages. If you have to maintain more packages, then this solution becomes both tedious and error prone.
Do you agree with these statements John?
p.s.
Everything is possible to accomplish without use of third-party components. But I will ask the same question I have asked many times before. Is it worth it and what is the cost of avoiding it? Most of the companies doesn't have unlimited resources to throw after reinventing the wheel. If there is easier solution, why not use it and save the money for the custom business logic implementation?
Do I agree with the statements, no.
1. So you're saying that you need to be a programmer to use BCP? Do you have to be a programmer to use SQLCMD? These are basic DBA tools that all SQL Server DBAs should be familiar with. BCP is a utility, not a programming language. Do you really want a DBA that does not have an understanding of BCP designing and developing an SSIS solution? SSIS is a utility and I would argue that you need to be a programmer more so to use SSIS than BCP. I know, I know, not if they use 3rd party tools! Every SQL Server DBA should have a working knowledge of BCP, period. If they don't, they need to develop that skill set because it is one of the most widely used tools for working with large amounts of data in SQL Server.
2. So 5-10 SSIS packages as part of a solution is the acceptable number. How did you come up with this number? What about a large enterprise wide solution extracting data from numerous sources, transforming the data, and populating multiple data marts, ODS's and a data warehouse? More than 10 individual componenets makes it more error prone? I would argue that componentizing your SSIS solutions mitigates the impact of errors because the errant logic is local to a specific package. Wouldn't this make it easier to isolate and fix the error?
What ever happened to learning a new skillset on the job? Do third party tools have their uses, yes. I personally don't care to depend on tools that are not part of the standard installation becasue they can become a crutch and prevent me from developing the skillsets required to become proficient with the standard product. What happens when I go to apply at another company and I am asked as to my experience with SSIS programming? "Do I say, I'm a DBA, not a programmer! Sure I can use SSIS, as long as you buy a tool that does the programming for me." How will that go over?
-
John,
1. I'm talking about this statement: "Dynamically building out the BCP command"
This is requirement to be a programmer.
2. I agree that depending on one component, could cause all your dependent packages to fail. But then I would ask if componentizing is so evil, why the whole software industry is moving in that direction for the past 20+ years? You may fight against this direction, but even SSIS itself is implemented and based on component architecture. If Microsoft makes a fix in one of the standard components, all your packages may start to fail. Does this stop you from using the standard components? Having to make the same change in hundreds of places makes it error prone because it depends on the human element to do it right.
Your last statement, makes me feel sorry about your employer. You are not using the most cost-efficient solution for your employer, but the solution which makes YOU feel good. CozyRoc is not providing "crutches". If there is a solution on the market, but you insist on doing it your-way, that's fine. Just remember you are reinventing the wheel. If you have time to do it in this wasteful way, go ahead. Other people are looking for cost-efficient solutions.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (8/10/2009)
John,1. I'm talking about this statement: "Dynamically building out the BCP command"
This is requirement to be a programmer.
2. I agree that depending on one component, could cause all your dependent packages to fail. But then I would ask if componentizing is so evil, why the whole software industry is moving in that direction for the past 20+ years? You may fight against this direction, but even SSIS itself is implemented and based on component architecture. If Microsoft makes a fix in one of the standard components, all your packages may start to fail. Does this stop you from using the standard components? Having to make the same change in hundreds of places makes it error prone because it depends on the human element to do it right.
Your last statement, makes me feel sorry about your employer. You are not using the most cost-efficient solution for your employer, but the solution which makes YOU feel good. CozyRoc is not providing "crutches". If there is a solution on the market, but you insist on doing it your-way, that's fine. Just remember you are reinventing the wheel. If you have time to do it in this wasteful way, go ahead. Other people are looking for cost-efficient solutions.
1. How is this any different than writing a SELECT statement? Using the expression builder to build out a BCP statement using package level variables is conceptually no different than saying, SELECT 'bcp "SELECT COUNT(*) FROM MyTable" queryout Test.dat -T -c'? Concatenating string values together using the expression builder is far cry from programming.
2. Exactly where did I say componentizing is evil? I would appreciate it if you did not put words into my mouth. Your comments on this second point are off-topic. Maybe this has to do with my use of the word "componentizing". I was referring to breaking up a SSIS solution into individual packages, or components and not the use of components as tasks within a package. In that context, we were discussing using a multi-package solution so solve a business problem. Would you care to address any of the points that I brought up regarding this?
Exactly where did my feeling enter into this discussion? How do you know that I'd feel better creating my own solution? How can you say that your solution is more cost effective than anything that I could create? By your logic, you are saying that anyone who knows about your product but chooses not to purchase and use it is wasteful and not providing their employer with the most cost effective solution?
That's a pretty bold statement.
-
I said many times to myself that i am not gonna reply tothis thread. But, cannot hold anymore as I receive email everytime there is reply from u all. But I like it though.
I think I totally agree with JOHN. (CozyRoc, I don't have any personal issues with you, nor with ur tools. I know, they are good as I have mentioned u earlier that i use them in my dev machine often).
John is right a DBA or developer should have some knowledge about BCP and few SSIS tools.
SSIS is not programming ( I hate programming and I agree that I am not good at it either. Although I have build few complex ssis packages in my work). The only difficult I face is when I have to use Script task or script component where VB or C# come into play.
And when we talk about 5 -10 ssis packges, it is not that much. We all know, the more the coplex the package is, the harder it is for maintainance and debugging. and personally I think it becomes more prone to error than few simpel packages.
In this case, I would go with John's second solution to have individual package for each load type and call them through master based on file type.
Also, I would like to point here is that not every company out there is willing to pay extra buck for third party tools. There might be other greater tools out there, but if company started to buy each and everyone, i guess we all know what will be the end results. Whether, you are in a director level position or manager or evn a developer, we all know we all hev to work under limited resources and make the best out of it. if this was not the case, then why not every compnay hire the great guy from all over the place and make the best solutions out there. But these never happens, becoz compnay wants to spend with their budget and get the best one with their budget.
All i have to say finally, is tools are great, whether it is third part tools or tools that you have as a standrad purchase. But, little extra efforts on what you have will not hurt anyone.
-
John Rowan (8/10/2009)
CozyRoc (8/10/2009)
John,1. I'm talking about this statement: "Dynamically building out the BCP command"
This is requirement to be a programmer.
2. I agree that depending on one component, could cause all your dependent packages to fail. But then I would ask if componentizing is so evil, why the whole software industry is moving in that direction for the past 20+ years? You may fight against this direction, but even SSIS itself is implemented and based on component architecture. If Microsoft makes a fix in one of the standard components, all your packages may start to fail. Does this stop you from using the standard components? Having to make the same change in hundreds of places makes it error prone because it depends on the human element to do it right.
Your last statement, makes me feel sorry about your employer. You are not using the most cost-efficient solution for your employer, but the solution which makes YOU feel good. CozyRoc is not providing "crutches". If there is a solution on the market, but you insist on doing it your-way, that's fine. Just remember you are reinventing the wheel. If you have time to do it in this wasteful way, go ahead. Other people are looking for cost-efficient solutions.
1. How is this any different than writing a SELECT statement? Using the expression builder to build out a BCP statement using package level variables is conceptually no different than saying, SELECT 'bcp "SELECT COUNT(*) FROM MyTable" queryout Test.dat -T -c'? Concatenating string values together using the expression builder is far cry from programming.
2. Exactly where did I say componentizing is evil? I would appreciate it if you did not put words into my mouth. Your comments on this second point are off-topic. Maybe this has to do with my use of the word "componentizing". I was referring to breaking up a SSIS solution into individual packages, or components and not the use of components as tasks within a package. In that context, we were discussing using a multi-package solution so solve a business problem. Would you care to address any of the points that I brought up regarding this?
Exactly where did my feeling enter into this discussion? How do you know that I'd feel better creating my own solution? How can you say that your solution is more cost effective than anything that I could create? By your logic, you are saying that anyone who knows about your product but chooses not to purchase and use it is wasteful and not providing their employer with the most cost effective solution?
That's a pretty bold statement.
1. You are talking about subset of what it means a statement to be dynamic. How about if you have new columns? How are you going to process these? How are you going to map these from source to destination?
2. Thank you for clarification. I thought you were talking against "componentization". Good we are on the same page here 🙂 However this doesn't change the fact that having static data flows, makes you work harder and harder the more packages you have.
John,
If you investigate CozyRoc's products closer, you will encounter common theme. We want to make YOU work smarter, not harder (you call this having "crutches"). The common theme is reuse and common scenarios generalization. We collect feedback from people. We also spend considerable time researching the forums and the issues people encounter. Then we try to come up with solutions, to make the SSIS development process smoother. Even the improvements are totally dependent on the user feedback. I don't know if you can get any more user-driven than this.
p.s.
Check the original post date, of the thread you are now commenting. We collect all related posts and try to integrate these as requirements into a working technology. If we thought there is easier solution, we wouldn't have spent even a second trying to come up with useless garbage.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Gkhadka,
Thank you for the meaningful post and kind words.
I'm sure you realize, when you talk about "little extra efforts", this translates directly into time and money. As people say there is no such thing as free lunch.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (8/10/2009)
John Rowan (8/10/2009)
CozyRoc (8/10/2009)
John,1. I'm talking about this statement: "Dynamically building out the BCP command"
This is requirement to be a programmer.
2. I agree that depending on one component, could cause all your dependent packages to fail. But then I would ask if componentizing is so evil, why the whole software industry is moving in that direction for the past 20+ years? You may fight against this direction, but even SSIS itself is implemented and based on component architecture. If Microsoft makes a fix in one of the standard components, all your packages may start to fail. Does this stop you from using the standard components? Having to make the same change in hundreds of places makes it error prone because it depends on the human element to do it right.
Your last statement, makes me feel sorry about your employer. You are not using the most cost-efficient solution for your employer, but the solution which makes YOU feel good. CozyRoc is not providing "crutches". If there is a solution on the market, but you insist on doing it your-way, that's fine. Just remember you are reinventing the wheel. If you have time to do it in this wasteful way, go ahead. Other people are looking for cost-efficient solutions.
1. How is this any different than writing a SELECT statement? Using the expression builder to build out a BCP statement using package level variables is conceptually no different than saying, SELECT 'bcp "SELECT COUNT(*) FROM MyTable" queryout Test.dat -T -c'? Concatenating string values together using the expression builder is far cry from programming.
2. Exactly where did I say componentizing is evil? I would appreciate it if you did not put words into my mouth. Your comments on this second point are off-topic. Maybe this has to do with my use of the word "componentizing". I was referring to breaking up a SSIS solution into individual packages, or components and not the use of components as tasks within a package. In that context, we were discussing using a multi-package solution so solve a business problem. Would you care to address any of the points that I brought up regarding this?
Exactly where did my feeling enter into this discussion? How do you know that I'd feel better creating my own solution? How can you say that your solution is more cost effective than anything that I could create? By your logic, you are saying that anyone who knows about your product but chooses not to purchase and use it is wasteful and not providing their employer with the most cost effective solution?
That's a pretty bold statement.
1. You are talking about subset of what it means a statement to be dynamic. How about if you have new columns? How are you going to process these? How are you going to map these from source to destination?
2. Thank you for clarification. I thought you were talking against "componentization". Good we are on the same page here 🙂 However this doesn't change the fact that having static data flows, makes you work harder and harder the more packages you have.
John,
If you investigate CozyRoc's products closer, you will encounter common theme. We want to make YOU work smarter, not harder (you call this having "crutches"). The common theme is reuse and common scenarios generalization. We collect feedback from people. We also spend considerable time researching the forums and the issues people encounter. Then we try to come up with solutions, to make the SSIS development process smoother. Even the improvements are totally dependent on the user feedback. I don't know if you can get any more user-driven than this.
p.s.
Check the original post date, of the thread you are now commenting. We collect all related posts and try to integrate these as requirements into a working technology. If we thought there is easier solution, we wouldn't have spent even a second trying to come up with useless garbage.
We obviously disagree on this. I would move that, for this example, having multiple packages makes maintaining the solution easier. Each package performs a specific set of tasks for a specific data source making maintenance, troubleshooting, and scaling the solution easier than if one package dynamically handled the logic for each file type.
Again, I'd appreciate if you don't put words in my mouth. Please go back and re-read the context in which i used the term crutch. I did not say that working smart instead of working hard was a crutch.
The only common theme that I'm seeing is that you have been posting about your product in this forum without actuallyl providing solutions for how your product will help. All I see is a number of threads claiming that you have something that 'may' help. For the posts that you've participated in, will it help or not?
http://www.sqlservercentral.com/Forums/Topic732460-148-1.aspx#bm766504
Here's a thread where 2 very simple solutions were given to a problem and you claim that your product 'may help'. Will it? How much time would you expect the poster to spend to determine if your product would help for this problem? Do you believe that purchasing your product is the most cost effective way for this poster to solve his problem?
http://www.sqlservercentral.com/Forums/Topic596864-148-1.aspx#bm766383
Here's another one where a poster was given a detailed description of how to solve their problem. What did you post?
CozyRoc has enhanced Data Flow Task Plus to support dynamic data flows. We will be happy to hear your feedback.
Exactly how would the enhancements help the poster? Would they at all? Would your product be more cost effective than the other suggestions?
http://www.sqlservercentral.com/Forums/Topic765357-148-1.aspx#bm766260
Here's another one
We have just released beta with an enhancement, which might help. We have improved the CozyRoc Data Flow Task Plus to support dynamic data flows. We will be happy to hear your feedback.
Again, it 'might' help. Will it help with this specific problem or not? Exactly how can it be used to solve the problem?
You know, I noticed the other day that you have replied to many threads with actual, real advice on how to solve a problem so I thought that you may not just be another guy promoting another product (several have come and gone through SSC over the years). Since then, you've had a number of thread hijacks where your sole purpose is to gain exposure for your product that may or may not actually help the poster with their problem.
-
John,
I think this conversation is going nowhere. Anyone who follows this will agree I gave my best. As I said earlier, if you don't see a point of using third party products, then don't use them. This just proofs we have yet to win your and other people's accolades.
In the meantime good luck with your SSIS projects and have a great day.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Cozy...How do you I download this task - Data Floe Task Plus?..
Here is waht I am trying to do..
We have 108 Sql history tables where data were stored for each day load. They are taking lots of space. SO now I am trying to export those data to flat file for each month and zip and store in some place and truncate those History tbls to free spaces. The problem is I don't want to create 108 differnt connections for each flat file as they have different structure.
SO i knew we had discuss this before..But I couldn't find a place to dowload this task when I visisted the website.
Thanks in advance
-
Download and install SSIS+ from here. Then go to the control flow toolbox and enable the Data Flow Task Plus. If you have issues, do not hesitate to contact us.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Thanks....I downloaded it.......
I will let you know once I use it on my SSIS or if there is more info i need on it..........
-
CozyRoc.........
Is there any example SSIS that uses this DataFlow Task Plus........I am finding little hard to configure this task. .......
-
I have sent you a private message.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply