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 |
|
rcorp
Starting Member
12 Posts |
 Posted - 2012-07-23 : 19:49:18
|
| Hi guys,I wanted to take time to appreciate your efforts for helping other people. I used to browse for some help in SSIS. However this time I have a situation where in I am clueless on how to achieve this.I have a source data with 4 columns. I want to save the data to a flat file destination after transformations.My source data looks like belowLight_wave 1250 West 2012-07-14Princeton9 350 East 2012-06-25Lewis3-ville 745 Mid-west 2012-05-29Cannon dr 850 West 2012-07-04I wanted to error out the rows whose column 1 values has a number in it. I am fine with _ and - though. Since princeton9 has number in the name, i want to capture it during the tranformation process. So after the transformation phase, I want rows 1 and 4 sent to my flat file destination and want rows 2 and 3 in my error output captured to another flat file.I am searching all over the place but couldnt figure out a way to achieve it. Any help would be appreciated and thanks for your time. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-23 : 21:28:30
|
| Inside your data flow task with flat file source add a script task. Add a new variable inside script task having boolean type and give it a namethen use function like given in below linkhttp://pragmaticworks.com/cheatsheet/then add a conditional task and add two output (numeric and nonnumeric)for numeric give condition likeDerivedCol == (DT_BOOL) "True"and for non numericDerivedCol == (DT_BOOL) "False"and then link both the outputs to two flat file destinations------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rcorp
Starting Member
12 Posts |
Posted - 2012-07-24 : 13:11:20
|
quote:
Originally posted by visakh16
Inside your data flow task with flat file source add a script task. Add a new variable inside script task having boolean type and give it a namethen use function like given in below linkhttp://pragmaticworks.com/cheatsheet/then add a conditional task and add two output (numeric and nonnumeric)for numeric give condition likeDerivedCol == (DT_BOOL) "True"and for non numericDerivedCol == (DT_BOOL) "False"and then link both the outputs to two flat file destinations------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi visakh,Thanks for your suggestion. I am new to this and not sure if I will be able to do it. is it possible to provide a sample code. i am also trying to figure it out. Many thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-24 : 13:28:22
|
| that link has sample function which you can use it inside your script task.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rcorp
Starting Member
12 Posts |
Posted - 2012-07-24 : 17:38:33
|
quote:
Originally posted by visakh16
that link has sample function which you can use it inside your script task.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
[Remove any non-numeric data from a column] <---- Is this the function I have to use? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-24 : 22:45:19
|
| yep...tweak it a little to return a boolean result instead of eliminating non numeric data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Conditional split?