SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How comma separated values are divided into rows?


How comma separated values are divided into rows?

Author
Message
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26088 Visits: 17536
Phil Parkin (2/6/2014)

Out of interest, how did you get 329 points here without posting anything?



QOTD probably.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3800 Visits: 1465
Since "I have the input:" doesn't specify where it's coming from, your T-SQL solutions seem to assume that the data is coming from a SQL database. If not, there are other required steps to insert it into a database, transform it, and clean up afterward.

If this data is coming from outside SQL, say a text file, and your goal is to transform it in SSIS before further processing and/or database insertion, a script transform component might be the best answer. Parse the first two fixed columns of each input row, then use String.Split on the city list and an AddRow method in a loop over the results.

I like T-SQL as much as the next guy, but when someone is talking about transforming data in SSIS I don't assume T-SQL is the only answer.

PS I'm the guy with a couple of SSIS packages that have evolved into one script task labeled "Do everything". I think they're perfectly maintainable, but nobody else around here has actually tried it.



Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18470 Visits: 20436
I like T-SQL as much as the next guy, but when someone is talking about transforming data in SSIS I don't assume T-SQL is the only answer.


You seem to be implying that we do, is that correct?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3800 Visits: 1465
I was just making the observation that the original post did not say where the data was coming from or where it was going. However clever the T-SQL solutions are, I saw no mention of the possibility of any other method. There were no questions about the data source, destination, or number of rows.

Usually in these forums I see a lot of enlightened discourse about possible gotchas or other aspects of a problem that would never occur to me, and find it very informative. This time I felt the proposed solutions may not have had enough variety. I doubt that most of you would have any trouble implementing the script transform I described, it's not a shockingly clever idea. It just seemed to have been overlooked.



Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18470 Visits: 20436
Scott Coleman (2/6/2014)
I was just making the observation that the original post did not say where the data was coming from or where it was going. However clever the T-SQL solutions are, I saw no mention of the possibility of any other method. There were no questions about the data source, destination, or number of rows.

Usually in these forums I see a lot of enlightened discourse about possible gotchas or other aspects of a problem that would never occur to me, and find it very informative. This time I felt the proposed solutions may not have had enough variety. I doubt that most of you would have any trouble implementing the script transform I described, it's not a shockingly clever idea. It just seemed to have been overlooked.


Even though the very first response contains this?

If this is not possible, you'll have to implement it with a script component.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3800 Visits: 1465
Mea culpa, I overlooked one line in the first response. I'll just go shoot myself now.

Now you're free to criticize Jeff Moden (for whom I have a tremendous amount of respect) for saying he wouldn't do this in SSIS at all.



Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27365 Visits: 13268
Scott Coleman (2/6/2014)
Mea culpa, I overlooked one line in the first response. I'll just go shoot myself now.


One of the most important lines in the whole thread!

Scott Coleman (2/6/2014)

Now you're free to criticize Jeff Moden (for whom I have a tremendous amount of respect) for saying he wouldn't do this in SSIS at all.


That would be like criticizing Picasso for not wanting to using crayons instead of a paint brush.

Regarding your comment that a TSQL solution might not fit every source:
that is certainly true, but you can always dump the data into a staging table and then use TSQL.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3800 Visits: 1465
Yes, you could import the data to a staging table. But if neither the source nor destination is SQL, the overhead of creating the table and cleaning up the table and possibly two extra hits to the network bandwidth to copy the data may not be justified for something even a lowly laptop could easily handle by itself.

Let me try a counter example. A co-worker once showed me an SSIS package he was very proud of. It had a huge data flow with conditional split and derived column transformations. It was a work of art, but it could also have been done in the source SQL statement with some CASE statements and subqueries, and probably with less development time. The whole data flow could be reduced to an INSERT/SELECT statement in a SQL task, without dragging the data over the network twice so it can be handled one row at a time in an SSIS data flow. It was a valid solution, and might be good for some kinds of non-relational data sources, but in this case it was not the best solution.

My whole point was that often the questions of data source, destination, and possibly volume should take precedence over what language the solution is implemented in. For all we know the data is coming from a web service and is to be reformatted in XML and sent to an FTP site. I would certainly go with the T-SQL string splitter if the data source is SQL Server, but I would want to get that pinned down before I started debating which T-SQL string splitter implementation to use.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86039 Visits: 41095
Scott Coleman (2/6/2014)
Since "I have the input:" doesn't specify where it's coming from, your T-SQL solutions seem to assume that the data is coming from a SQL database. If not, there are other required steps to insert it into a database, transform it, and clean up afterward.

If this data is coming from outside SQL, say a text file, and your goal is to transform it in SSIS before further processing and/or database insertion, a script transform component might be the best answer. Parse the first two fixed columns of each input row, then use String.Split on the city list and an AddRow method in a loop over the results.

I like T-SQL as much as the next guy, but when someone is talking about transforming data in SSIS I don't assume T-SQL is the only answer.

PS I'm the guy with a couple of SSIS packages that have evolved into one script task labeled "Do everything". I think they're perfectly maintainable, but nobody else around here has actually tried it.


I like SSIS as much as the next guy, but when someone is talking about transforming data in any environment, I don't assume that SSIS is the only answer. :-P



Don't be offended. I just had to have some fun with that.

You're correct that we don't know what the source of the data is. I'm not sure that makes a huge difference in this case, though because, whether it comes from a file or a table, the answer will be nearly identical whether it be in SSIS or T-SQL. That may be a part of why the answers were more limited than they normally are for other problems.

Shifting gears to the idea that you came up with using "Split.String" and a loop in SSIS, I couldn't have come up with that partiicular answer because I really don't use SSIS for what seem to be trivial imports of such data and so I didn't even know that "Split.String" existed. That's good to know and thank you for posting it.


Now you're free to criticize Jeff Moden (for whom I have a tremendous amount of respect) for saying he wouldn't do this in SSIS at all.



Heh... I'm used to, welcome, and embrace criticism. It's one of the tools I use to learn new things. For example, if someone could prove to me that using SSIS is twice as fast or twice as easy over T-SQL related methods to import text files, I'd take that into serious consideration and would fire up SSIS to start teaching myself more about it. Watching people work with it in previous jobs leads me to believe that isn't the case though and so, much like people that say T-SQL isn't the only answer, I suggest that SSIS isn't the only answer, either. ;-)

That being said, I'd love to see the actual solution in SSIS for this problem if you'd like to post it. I'd also be interested in knowing how long it took from the git to actually have done a test run that works.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8597 Visits: 7660
Jeff Moden (2/6/2014)
Heh... I'm used to, welcome, and embrace criticism. It's one of the tools I use to learn new things. For example, if someone could prove to me that using SSIS is twice as fast or twice as easy over T-SQL related methods to import text files, I'd take that into serious consideration and would fire up SSIS to start teaching myself more about it. Watching people work with it in previous jobs leads me to believe that isn't the case though and so, much like people that say T-SQL isn't the only answer, I suggest that SSIS isn't the only answer, either. ;-)


Challenge accepted. Cool

There are places where T-SQL definately outperforms SSIS. Anytime you have to coagulate the stream (I use that term loosely, but it's appropropriate) for aggregations or sorts, it dies a horrible death.

There are transformations (particularly cascading updates and string manipulations) where SSIS rules.

Jeff, do you have SSIS 2k8 available to you or do you need said examples in 2k5?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search