February 4, 2014 at 11:32 pm
Hi Experts,
I have the input:
rankcountrycity
1indiahyderabad, bangalore, delhi, shimla
2usanewjersey, newyork, washington, texas
3uklondon, greenland, denmark, italy, spain
expected output:
rank country city
1 india hyderabad
1 india banalore
1 india delhi
1 india shimla similarly for rank 2 and 3
Can anyone help me how to achieve this in SSIS BIDS. What logic should be used and in which componenet.??
Thank you in Advance,
Regards,
Vijayvanamala
February 5, 2014 at 12:34 am
You need to use the Unpivot transformation.
How To Use the Unpivot Data Flow Transform in SQL Server Integration Services SSIS[/url]
The unpivot however accepts only a fixed number of columns as input though. So you have to make sure the city column is splitted into multiple columns and that this number is fixed.
If this is not possible, you'll have to implement it with a script component.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 5, 2014 at 8:22 am
I wouldn't do this part in SSIS. Post some readily consumable data according to the first link in my signature line below under "Helpful Links" and if I don't get to it, I'm sure that someone else will.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2014 at 8:31 am
I am pretty sure that Jeff was thinking of using the DelimitedSplit8K function for this.
I create the ddl and sample data for you to demonstrate how you should post this in the future.
create table #Something
(
CityRank int,
Country varchar(10),
City varchar(200)
)
insert #Something
select 1, 'india', 'hyderabad, bangalore, delhi, shimla' union all
select 2, 'usa', 'newjersey, newyork, washington, texas' union all
select 3, 'uk', 'london, greenland, denmark, italy, spain'
select CityRank, Country, ltrim(x.Item)
from #Something s
cross apply dbo.DelimitedSplit8K(s.City, ',') x
drop table #Something
You will need to read the article in my signature about splitting strings. In there you will find how to make the DelimitedSplit8K function on your server. Make sure you read and understand the technique here.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 9:18 am
Sean Lange (2/5/2014)
I am pretty sure that Jeff was thinking of using the DelimitedSplit8K function for this.
Correct and well done. Thanks for the cover, Sean.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 5, 2014 at 9:22 am
Jeff Moden (2/5/2014)
Sean Lange (2/5/2014)
I am pretty sure that Jeff was thinking of using the DelimitedSplit8K function for this.Correct and well done. Thanks for the cover, Sean.
My pleasure.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 11:51 pm
I tried the UNPIVOT transformation for this problem and after pratting about with it for a while in an attempt to make it work, I have come down soundly on the side of Jeff and Sean - do this outside of SSIS.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 5, 2014 at 11:57 pm
Phil Parkin (2/5/2014)
I tried the UNPIVOT transformation for this problem and after pratting about with it for a while in an attempt to make it work, I have come down soundly on the side of Jeff and Sean - do this outside of SSIS.
And if you think the UNPIVOT has an unholy GUI, try the PIVOT one for a change...
:hehe:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2014 at 1:11 am
Would it be ok for you to use recursive CTE ? ..something like
create table #Something
(
CityRank int,
Country varchar(10),
City varchar(200)
)
insert #Something
select 1, 'india', 'hyderabad, bangalore, delhi, shimla' union all
select 2, 'usa', 'newjersey, newyork, washington, texas' union all
select 3, 'uk', 'london, greenland, denmark, italy, spain'
--SELECT * FROM #Something
;WITH CTE AS
(
SELECT CityRank,Country,City,SUBSTRING(CITY,1,CHARINDEX(',',CITY,1)-1) AS C_CITY,
SUBSTRING(CITY,CHARINDEX(',',CITY,1)+1,LEN(CITY)) AS C_STR FROM #Something
UNION ALL
SELECT CityRank,Country,City,LTRIM(RTRIM(SUBSTRING(C_STR,1,CASE
WHEN CHARINDEX(',',C_STR,1) = 0 THEN LEN(C_STR)
ELSE CHARINDEX(',',C_STR,1) - 1
END ))),
SUBSTRING(C_STR,CASE
WHEN CHARINDEX(',',C_STR,1) = 0 THEN NULL
ELSE CHARINDEX(',',C_STR,1)+1
END,CASE
WHEN LEN(C_STR) = 0 THEN 1
ELSE LEN(C_STR)
END) FROM CTE
WHERE CHARINDEX(',',C_STR,1) >= 0 --OR
)
SELECT CityRank,Country,C_CITY AS CITY FROM CTE ORDER BY CityRank
I'm sorry for the formatting/colors ..as this is my first post and do not know how to post with SQL formatting being intact
February 6, 2014 at 1:28 am
chetan.deshpande001 (2/6/2014)
Would it be ok for you to use recursive CTE ? ..something like--Edit
I'm sorry for the formatting/colors ..as this is my first post and do not know how to post with SQL formatting being intact
OK ... perhaps, but the proposed Delimited8KSplit solution will outperform it, I'd bet my wife's shoes on that.
Out of interest, how did you get 329 points here without posting anything?
T-SQL formatting: All you need to do is enclose your code with {code="sql"}{/code} tags (use square brackets)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 6, 2014 at 7:22 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2014 at 7:56 am
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.
February 6, 2014 at 8:08 am
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 6, 2014 at 8:32 am
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.
February 6, 2014 at 8:36 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply