how to pull out numeric and text data from a varchar column by SSIS

Question

question

kaks_das asked Nov 10, '10

how to pull out numeric and text data from a varchar column by SSIS

-- table staging_tab CREATE TABLE [dbo].[staging_tab] ( [item] [varchar](50) NULL, [values] [varchar](50) NULL ) ON [PRIMARY]; -- the values in staging_tab INSERT INTO [staging_tab]([item], [values]) VALUES('car', 'bmw'); INSERT INTO [staging_tab]([item], [values]) VALUES('car', 'toyota'); INSERT INTO [staging_tab]([item], [values]) VALUES('no of door', '2'); INSERT INTO [staging_tab]([item], [values]) VALUES('no of door', '4'); --table item CREATE TABLE [dbo].[item] ( [item_id] [int] IDENTITY(1,1) NOT NULL, [item] [varchar](50) NULL ) ON [PRIMARY]; by using SSIS 2008 item table is populated having values

item_id item
1       car
2       no of door

-- table values CREATE TABLE [dbo].[values] ( [id] [int] IDENTITY(1,1) NOT NULL, [item_id] [int] NULL, [value_text] [varchar](50) NULL, [value_int] [int] NULL ) ON [PRIMARY]; the values in values table should be populated depending item id..if item\_id = 1 then value_text column is populated and if item\_id=2 then value_int is populated.

id  item_id  value_text  value_int
1   1        bmw         NULL
2   1        toyota      NULL
3   2        NULL        2
4   3        NULL        4

can anyone give any suggestion how to proceed to distribute the data to 2 different columns of same table based on item_id by SSIS using?

ssis

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-11-10T11:41:25Z

Fatherjack answered Nov 10, '10

Use a CASE statement. . . . SET value_text = CASE WHEN item_id = 1 then 'bmw' else Null end , value_int = CASE WHEN item_id=2 then '2' else NULL end . . .

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Nov 10, 2010 at 11:43 AM

@Fatherjack I was 42 seconds late :(

0 ·

Answer 2 · 2010-11-10T11:42:07Z

Oleg answered Nov 10, '10

One of the ways to have the sparse data is by using case statement. For example, insert into [dbo].[values]([item_id], value_text, value_int) select item.item_id, case when item.item_id = 1 then st.[values] else null end value_text, case when item.item_id = 2 then st.[values] else null end value_int from [dbo].[staging_tab] st inner join dbo.item item on st.item = item.item; -- running a select to check what was inserted: select * from [dbo].[values]; -- returns: id item_id value_text value_int ----------- ----------- ---------- ----------- 1 1 bmw NULL 2 1 toyota NULL 3 2 NULL 2 4 2 NULL 4 Hope this helps, Oleg

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-11-10T15:28:18Z

Daniel Ross answered Nov 10, '10

if you wanted to do it using pure ssis, Add a data flow and create two sources, one for your STAGING_TAB and the other for your ITEM table. sort them by ITEM, and then put a join component in and select inner join and join on ITEM. After the join, put a conditional split component in and put 2 case, one is item_id == 1 and the other item_id == 2 then put two OLEDB destinations in, both pointing to the VALUES table, the first dest put in case 1 from the split and insert the value to valuetext, and the other dest, map the value to valueint. and your done. this would work best if your staging tab is on a different server, but if your staging tab is on the same table, then just use the case statement in an execute SQL task in the control flow

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question