Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
blazing
Starting Member
1 Post |
 Posted - 2012-11-16 : 01:36:55
|
| 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
52326 Posts |
Posted - 2012-11-16 : 23:21:31
|
| You can simply Add a expression inside data flow like this in derived column taskLEN([StringCol])-LEN(REPLACE([StringCol],"|",""))then use it in a conditional task likeDerivedCol <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 tablethen 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
Split string and get the count using SSIS