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 |
|
ucal
Yak Posting Veteran
72 Posts |
 Posted - 2008-04-03 : 12:02:49
|
| I have a comma delimited data file TestData.txt of the form:123,asss,aweqrrr ,ssdsff , ,2wwwrwrr 434,sff,dgfdgd ,sffsfsfete ,sd ,dff I want to load this data into SQL Server tables so that the data will be imported without the trailing spaces(blanks) after each data element .Thus I will like to trim the data of the extra spaces (blanks) before import.Desired data to be loaded :123,asss,aweqrrr,ssdsff,,2wwwrwrr 434,sff,dgfdgd,sffsfsfete,sd,dffAny help will be most welcomed |
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-04-03 : 12:49:23
|
| Im not much of an expert at SSIS but i belived u can do that adding a Derived Column Task to your data flow (That is: in your dara flow u have your Flat File Source and u connect it to a Derived Column Task). At the Derived Column you especify the new columns for each of the files column and at the expression column you use the String Expression TRIM() and put inside your column i.e. TRIM(Column1). The expressions are case sensitive.RTRIM and LTRIM will also to the work for right and left spaces in the column. |
 |
|
|
ucal
Yak Posting Veteran
72 Posts |
Posted - 2008-04-03 : 13:44:59
|
| I have not used the derived colum before , can somebody point me in the right direction |
|
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-04-03 : 15:39:42
|
| Here we go ucal.I supose u know you have to be working inside a Data Flow (Drag a Data Flow component from the ToolBox to the Control Flow in case you havent done that).Now you double click your Data Flow and you'll be inside the Data Flow Task.First you have to drag a Flat File Source form the Toolbox which of course will be the Source of your data. Double Click it and you will have its editor. You need to create a new Connection Manager. It is going to be a Flat File Connection Manager (there u have to configure the columns of your Text File[if you need more details here let me know)After you're done configuring your Connection Manager and your Flat File Source you can click OK.Now you are going to drag a Derived Column Task from the Tool Box (Its inside the Data Flow Transformation Area of the Toolbox)And heres where u going to really fix your problem.Drag the green arrow of the Flat File Source to the Derived Column Task.Double Click it. Here you can convert any column you have to any Data Type, and do some operations with them.For removing spaces from a column here's what you are going to do:1.Expand the String Function Folder on the top right side of the editor.2.Drag the TRIM(<<character_expression>>) option to the expression column of the first row.3.Expand the Columns Folder.4.Drag the Column you want to fix inside the TRIM(<<character_expression>>) you dragged before. So it will be something like TRIM(ColumnName).5.You have to specify a new Column Name and the Data Type of the Column name.Click Ok and you're done.If you are going to insert this into a SQL Server Database a suggest you use an OLE DB Destination from the Data Flow Destinations (The Sql Server Destinations has several issues so dont use that).Inside your Ole Db Destination you will have to configure an Connection Manager for the SQL Server (Very similar to the Flat File Connection Manager) and you need to map the columns in the Mapping Tab. Mapping is very easy but you need to be sure that you choose the new Column you made in the Derived Column when you map it to the desire destination column.I tried to explain it the best i could since i just spent 2 days trying to do that. |
|
|
|
ucal
Yak Posting Veteran
72 Posts |
Posted - 2008-04-03 : 16:59:03
|
| Thanks LoudBliss, it worked like charm !!!!!!!!!! |
|
|
|
|
|
|
|
|
Removing Blanks in Data before import