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
Change is inevitable... Change for the better is not.