Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How comma separated values are divided into rows? Expand / Collapse
Author
Message
Posted Thursday, February 6, 2014 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's 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)
Post #1538645
Posted Thursday, February 6, 2014 7:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #1538669
Posted Thursday, February 6, 2014 8:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1538677
Posted Thursday, February 6, 2014 8:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #1538694
Posted Thursday, February 6, 2014 8:36 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:49 PM
Points: 4,973, Visits: 11,660
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1538697
Posted Thursday, February 6, 2014 8:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #1538710
Posted Thursday, February 6, 2014 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 11:20 PM
Points: 13,252, Visits: 10,133
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1538752
Posted Thursday, February 6, 2014 12:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #1538802
Posted Thursday, February 6, 2014 4:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 36,711, Visits: 31,159
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.



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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1538947
Posted Thursday, February 6, 2014 5:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 6,131, Visits: 7,168
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.

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
Post #1538948
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse