SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Split string and get the count using SSIS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

blazing
Starting Member

1 Posts

Posted - 11/16/2012 :  01:36:55  Show Profile  Reply with Quote
Hi all,

I am creating a pckage in SSIS in which there is a flatfile source and a destination table. The source contains a string in each row. The string should contain 5 entries delimited with '|' and it looks like this.

a|b|c|d|e

I need to split the string and determine there are 5 entries(delimited with '|') and then insert each entry to the destination table.

The task should fail in case there are less than 5 entries. i.e. a|b|c|d - it fails.

I am able to split the string and insert each entry to the 5 columns in the table. But I am not able to validate the number of entries.

How can I split the string and get the count and verify there 5 entries using SSIS. Please help.

Thanks in advance,

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/16/2012 :  23:21:31  Show Profile  Reply with Quote
You can simply Add a expression inside data flow like this in derived column task

LEN([StringCol])-LEN(REPLACE([StringCol],"|",""))


then use it in a conditional task like

DerivedCol <4 and in the output store results in a variable using rowcount transform and in next step check if variable >0 and fail package.

another way is to open file inside script task using Read() function and then parse and check if count of | is below 4 and raise error

If you dont want to do all this in SSIS, just add a simple data flow task to dump file data as a single value onto a table

then use t-sql inside execute sql task to check if there are rows in table with | below 4 and throw error from sql code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000