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.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Trying not to hate SSIS

Author  Topic 

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2012-02-21 : 06:32:04
I have some data that has been imported fine and I want to do some basic operations on some of the columns e.g. TRIM,

In a DTS package I would just do something like UPDATE Table SET Column = TRIM(Column) but I can't see any way of doing this in the data flow.

Googling came up with http://developers.de/blogs/nadine_storandt/archive/2006/12/07/How-to-Insert-or-Update-records-in-SSIS-DataFlow.aspx Is this the new way of doing this sort of thing? What am I missing here?

thanks

steve



-----------

What color do you want that database?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-21 : 06:58:11
You can use the Derived Column to do this.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2012-02-21 : 07:07:57
Thanks peso I'll take a look

-----------

What color do you want that database?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-21 : 07:56:03
The "Derived Column" is exactly how it works in a T-SQL query.
, CAST(Col1 AS INT) AS newColumn
, LTRIM(RTRIM(Col2)) AS newColumn2



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2012-02-21 : 08:32:34
I took a look and it does exactly what I want, in fact it does it better than I hoped.

I'm now trying to work out how to check if a string contains a numeric value and set it to null if it doesn't. With SQL I did something like

UPDATE MyTable
SET MyField = NULL
WHERE MyField like '%[^0-9]%'

steve

-----------

What color do you want that database?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:33:31
quote:
Originally posted by elwoos

I took a look and it does exactly what I want, in fact it does it better than I hoped.

I'm now trying to work out how to check if a string contains a numeric value and set it to null if it doesn't. With SQL I did something like

UPDATE MyTable
SET MyField = NULL
WHERE MyField like '%[^0-9]%'

steve

-----------

What color do you want that database?



you can achieve it using expression inside derived column using conditional operator.
for filter part use FINDSTRING to find location

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

Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2012-02-22 : 04:11:01
Thanks Visakh.

I've decided to take this sort out stuff out of the SSIS and put it in a stored procedure and just use SSIS for importing data and to then run the sproc.

steve

-----------

What color do you want that database?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-22 : 04:41:44
That's the way we handle our files.
SSIS is optimized for copying the data from the files to the staging table (Extraction), and then we run a procedure to do all the Transformation and Loading.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -