SSIS and Clean Data - SQL Server Forums

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 2005 Forums

SSIS and Import/Export (2005)

SSIS and Clean Data

Author

Topic

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-22 : 15:12:56

Hi, I'm not sure how to approach this.

I had created an SSIS Package to pick up a daily CSV download but it kept erroring. When I look at the data, I see things like this

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

and every few hundred rows there's a sub-total, so I get things like " LC amnt" in my float column.

What's the best way to get this into my SQL table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 23:53:08

put it in a staging table with all varchar fields, then do a check and move only valid rows to final table

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

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-23 : 10:00:03

Thanks, I've been at this for a day and half now, I can't see straight.

I'm very new to SQl so I can see there are articles about this but I don't understand them at all.

I'm now getting this msg. in my staging table, which I've set up to be Text Data Types

Error at Data Flow Task [OLE DB Destination [3548]]: Column "DocDate" cannot convert between unicode and non-unicode string data types.

What?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 12:02:58

use derived column transformation and cast them to non unicode type

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

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-23 : 16:43:11

Briliant, worked perfectly.

In my value column I have some headers coming into the Txt file download. To solve this my table Data type is Varchar50.

Is there a way in the Derived Field to Exclude where it says " LC amnt" so I can change my table to Float?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 01:34:35

yep. you can use REPLACE

http://msdn.microsoft.com/en-us/library/ms141196.aspx

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

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-24 : 08:29:16

Thanks again, sorry, I flinch as I ask this but as I've only been using this for a few weeks, it's the simple things that stump me.

In my SSIS Package within BI Studio, where do i need to paste the:

REPLACE(LCAmount, LC amnt,0)

I have the next step as pasting from the Statging table to the real table set up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 12:17:03

you should add it under derived transformation and select property "replace column"
Also replace should be like

REPLACE(LCAmount, "LC amnt","")

i think

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

PanzerAttack
Yak Posting Veteran

71 Posts

Posted - 2011-08-26 : 10:46:30

Awesome, perfect. Thanks muchly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-26 : 10:54:57

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources