SSIS - How to Write Case Statement In SSIS Package

Scenario:

We have received a text source file that has the abbreviation for Region Code. We want to convert these region codes to region name such as

If RegionCode is AS then RegionName="ASIA'

If RegionCode is NA then RegionName="NORTH AMERICA'

If RegionCode is EU then RegionName="EUROPE'

If non of above them "UNKNOWN".

Fig 1: Text Source File with ClientRegion

Solution: 

If this data would be in table we could have write a query with case statement like below

Select id,Name,ClientRegion, 
Case 
When ClientRegion='AS' THEN 'ASIA'
WHEN ClientRegion='NA' THEN 'NORTH AMERICA'
WHEN ClientRegion='EU' THEN 'EUROPE'
ELSE 'UNKNOWN' END AS RegionName
from dbo.Client

As we are reading the data from text file we can't write the TSQL statement. We can load this data to some table and then write the statement but we don't want to do that. We will use Derived Column Transformation to write expression to get our Region Name.

Step 1: 

Create your SSIS Package. Inside the SSIS Package, Bring Data Flow Task. As we need to read the data from text file, Bring Flat File Source and create connection to source file.

Once the connection is created, Bring Derived Column Transformation and connect to Flat File Source and write expressions as given below. These expressions will give us the results like our Case statement. 

ClientRegion == "AS" ? "ASIA" : ClientRegion == "NA" ? "NORTH AMERICA" : ClientRegion == "EU" ? "EUROPE" : "UNKNOWN"

Fig 2: Write Case Statement in Derived Column Transformation in SSIS Package

Step 2: 

Bring Multicast Transformation as test destination and then connect Derived Column Transformation to it. Add the Data Viewer so we can see the output. Run the SSIS Package and see the output.
Fig 3:Write Case Statement in SSIS Package By Using Derived Column Transformation


As we can see that the expression worked as expected.




69 comments:

  1. Nowadays, data has powerful capabilities that can transform your business’s face, especially when the competition is all around. Interested to know more about data intelligence, check out https://www.inetsoft.com

    ReplyDelete
  2. Nice post. I was checking constantly this blog and I’m impressed! Extremely useful info specially the last part I care for such information a lot. I was seeking this certain info for a long time. Thank you and good luck.write for us

    ReplyDelete
  3. Shop beautiful womens cashmere sweaters, cardigans and scarves. Chic and stylish. Made in EU. Free shipping worldwide. cashmere long cardigan

    ReplyDelete
  4. Tours | Blogging Journey for beginner's provides the guide of Blogging, WordPress, SEO, Affiliates, Hosting, Theme, and online... Best Page Builder Plugins

    ReplyDelete
  5. Urban VPN MOD APK is a great VPN and all of their users share their network resources to help each other unlock the Internet. Urban VPN MOD APK

    ReplyDelete
  6. Royal Tangkas is one of the favorite agile gambling games because it has a simple and fast style of play. royaltangkas

    ReplyDelete
  7. Sbowin & Joinsbo is an official Sbobet alternative link site that is also the most famous, especially in Indonesia. login sbobet

    ReplyDelete
  8. Such sites are important because they provide a large dose of useful information ... Satta king 786

    ReplyDelete

  9. Wonderful article. I love your style of writing. It's inspiring , and I am happy with your writing style. Visit my website.
    Black satta

    ReplyDelete
  10. Keep your expectations low
    In case you're participating in the Satta king fast and Matka games for the essential time then, at that point, keep the assumption for winning absolute bottom. Maybe you wish to do the Satta King 786 different times, notwithstanding how that is anyway exclusively you might be prepared to win.

    ReplyDelete
  11. 389Sports situs resmi agen joker123 dan joker388, download aplikasi joker123 apk, link alternatif, deposit via pulsa 25 ribu tanpa potongan. joker388

    ReplyDelete
  12. By playing using the official site, you can get many benefits, such as playing hundreds of online slot games with only 1 ID, the Joker Shoot Fish Online site 389Sports also always guarantees your safety and comfort when playing. joker123 deposit pulsa

    ReplyDelete
  13. Trusted online capsa stacking gambling site 389sports, playing capsun online, deposit 25 thousand via credit & e-wallet, 10% new member bonus. capsa uang asli

    ReplyDelete
  14. How to play Satta King game?
    With the advancement of technology, the rules of the game are also changing. A lot of differences have been noticed between the previous Satta King games and the current games. In the past, some Satta number written on pieces of paper were placed in containers or pots. The person who chooses the correct number is declared the Satta king. That means the person is the winner. Nowadays people like to play pc games. Choose the game and start playing.

    ReplyDelete
  15. The online fish shooting gambling game or fishing game itself is now available and you can play on a 389Sports site that offers online fish shooting gambling games or fishing games. Over time, the online fish shooting gambling game has become a very popular game. This is because the game itself is very colorful and interesting for bettors to play tembak ikan online

    ReplyDelete
  16. hubslotjoker info. HUBJOKER888 . Online slots on mobile phones. HUBJOKER888 , new online slots website, Joker, Auto top-up system, deposit ... joker สล็อต

    ReplyDelete
  17. These online slots games are very popular. and has the most players since Slotxo app has opened for service As an example, it can be seen that the online slots games from SLOTXO have different game themes. Whether it's a Safari style, a Chinese style, a hero style and many other genres that are waiting for you to open up a new experience in playing online slots. pg slot

    ReplyDelete
  18. best football odds This must be accepted that For anyone who wants to play football betting Really need this camp because the water price is up to 4 bucks. You have to understand that first. To press the ball will be very profitable, it depends on the goal scoring odds. ufa th

    ReplyDelete
  19. free credit, no deposit required, no sharing Web activities throughout the month Whether it's free credit giveaways, birthday giveaways, pg slots and many other great events Looking forward to having fun here. pg slot

    ReplyDelete
  20. ll casino games on SAGAME88 have a minimum bet of only 5 baht, a maximum of 1,000,000 baht, a minimum deposit of 1 baht, fast auto-deposit system, 24-hour care team. คาสิโนออนไลน์ สล็อต

    ReplyDelete
  21. Football betting, the most popular UFABET football betting website at the national level, the direct website does not go through the agent, deposit-withdraw, no minimum, fast, ready to serve 24 hours a day. ไพ่แคง ออนไลน์

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. How to apply for slot xo with 3 easy steps Friends can play slots (Slot Online). The team has prepared a method for applying for slots. can apply through the website Register for online slots ดาวน์โหลดpg slot

    ReplyDelete
  24. Deposit and withdraw with a reliable automated system There are more than 100,000 users per day. Unlimited deposits-withdrawals. Login to play online slots here 55xo ... joker สล็อต

    ReplyDelete
  25. This is what I’ve been searching for days. Extremely useful. Thank you for sharing! สล็อตPG aoom123.

    ReplyDelete
  26. These are some great tools that i definitely use for SEO work. This is a great list to use in the future..
    dg

    ReplyDelete
  27. เกมออนไลน์ที่ได้รับความนิยมมากที่สุดในตอนนี้ปี 2022
    joker slot
    joker gmaing

    ReplyDelete
  28. Thank you very much for writing such an interesting article on this topic. This has really made me think and I hope to read more. 918kisspg.com

    ReplyDelete
  29. Hi there! You make so many good points here that I read your article a couple of times. This is really nice content for your readers. MCCQE part 2

    ReplyDelete
  30. "I am extremely impressed along with your writing talents as well as with the structure for your blog.
    สล็อต66 สล็อตxo สล็อต11"

    ReplyDelete
  31. I will right away clutch your rss as I can’t find your email subscription link or e-newsletter service. Do you have any? Please let me realize so that I could subscribe. Thanks. I will right away clutch your rss as I can’t find your email subscription link or e-newsletter service. Do you have any? Please let me realize so that I could subscribe. Thanks.

    ReplyDelete
  32. Koçak Vize Vize Hizmetleri Vize işlemleri Elçiliğe Gitmeden Vize Alma Online Vize Başvurusu İşlemleri Vize Danışmanlık Firması olarak hizmetinizdeyiz Finlandiya vizesi

    ReplyDelete
  33. Because I can't find your email subscription link or e-newsletter service, I'll immediately grab your rss. Have you got any? Please let me understand so I can subscribe. Thanks. Because I can't find your email subscription link or e-newsletter service, I'll immediately grab your rss. Have you got any? Please let me understand so I can subscribe. Thanks. wool socks

    ReplyDelete
  34. We are professional Movers and packers in Dubai. We have experienced and standard team for Movers in Dubai

    ReplyDelete
  35. Longlevens Windows provide windows + door installation, we also offer customers a quality repair service for windows, doors and locks in Gloucestershire
    Longlevens door repairs

    ReplyDelete
  36. Preparing and shifting to a new house is no easy task to do. Because too many things or tasks to do at once give stress and make things complicated. But hiring a professional moving team this scary work does not have to be complicated. And it exactly so much happens that Number 1 “Best Mover” is one of the much dependable Packing and Moving company. So, if you need a team of professional packers and Movers that have handled any type of moving in the past – we assure you that we’re the best company to call!
    https://www.bestmover.ae/

    ReplyDelete
  37. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.. lifeguard class

    ReplyDelete
  38. Suppose you’ve worked for a long time in the same field or possess a particular interest that you would like to share with others or even teach to others. In that case, you may have the experience and knowledge to instruct, coach or collaborate with others in the area you are interested in.
    link building

    ReplyDelete
  39. "I'm thoroughly impressed by Pestclean's efficient and reliable pest control. Living pest-free has never been easie Pestclean

    ReplyDelete
  40. User engagement metrics, such as bounce rate and time on site, can impact SEO rankings.
    social media

    ReplyDelete
  41. It is a great website.. The Design looks very good.. Keep working like that!.
    24 hour plumbing Manchester

    ReplyDelete
  42. Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.
    home care In Manchester

    ReplyDelete
  43. Remote work has become a new normal for many companies. It's important to find ways to maintain productivity and team cohesion in this environment. better business bureau

    ReplyDelete
  44. The rise of social entrepreneurship shows that businesses can make a profit while also making a positive impact on society. better business bureau

    ReplyDelete
  45. "The return and exchange policies on e-commerce websites are quite flexible.Thai Silk Bed Sheets

    ReplyDelete
  46. The mix of vintage and modern pieces in this collection is a winning formula.findlatestfashiontrends

    ReplyDelete
  47. When you are planning a move, the movers in Dubai can help you make the transition by shifting your physical goods in a logical and safe manner. Through consistent handwork, dedication and honest services, Pack2Go has earned a strong reputation as a reliable company for both national and international moving solutions.

    ReplyDelete
  48. "Watching Trixie's tongue tricks is like taking a journey into the unexplored territories of human abilities. It's quirky, unexpected, and undeniably entertaining. Who knew that a tongue could be such a captivating performer?Trixie Tongue Tricks

    ReplyDelete
  49. Give your pets the royal treatment with specialized pet grooming at Anastasia's Acres Dog Boarding. From bathing and brushing to nail trimming and ear cleaning, our gentle touch and loving approach will keep your furry friends looking and feeling their absolute best.

    ReplyDelete
  50. Enhance your marketing materials with our photorealistic 3D renderings that leave a lasting impression.3D Architectural Animation Drawing Services

    ReplyDelete
  51. WordLenyTimes is not just a platform; it's a linguistic playground where words dance and ideas flourish.Wordle nytimes

    ReplyDelete
  52. In conclusion, TCU Football isn't just a team; it's a living legacy. From its inception to the present day, the team's journey is a tale of triumphs, challenges, and unwavering dedication to the sport.
    big xii baseball standingsbig xii baseball standings

    ReplyDelete
  53. Preserve the beauty and emotion of your wedding day with Splus Wedding's professional wedding video services.
    visit our website

    ReplyDelete
  54. When I originally commented I clicked the -Notify me
    Prothots

    ReplyDelete
  55. It seems promising, but I'm still a little sceptical. It seems to me understandable. I'm going to look into it more and make my own decisions. treescene

    ReplyDelete
  56. Sometimes it is what's more a reasonably fine text that him and i unquestionably liked investigating. Not always every day which will have fun with the probability to see a supplement. Akhuwat Loan

    ReplyDelete
  57. I'm not against the agree with the things you suggested. https://nuwallpaper.org/

    ReplyDelete
  58. The Oregon Adventure Bucket List Scratch Off is a thrilling companion for adventurers looking to explore the diverse landscapes and experiences Oregon has to offer. Each scratch-off panel reveals a unique adventure waiting to be uncovered, from hiking through the stunning Columbia River Gorge to surfing along the rugged coastline of the Pacific Ocean. This interactive bucket list encourages travelers to immerse themselves in Oregon's natural wonders and cultural gems, making it an essential tool for those seeking memorable outdoor experiences. oregon adventure bucket list scratch off

    ReplyDelete