How to add text before delimiter ^ in SSIS from CSV file

Question

How to add text before delimiter ^ in SSIS from CSV file

jn93 671

Hi, Lets say I will received the data like below in flat file source. For each action done, they multiply it with ^. How can I tabulate this in my sql database? Anyone please help. Appreciate it.

ZoeHui-MSFT 41,491 Reputation points

2022-10-20T01:29:09.57+00:00

Hi @jn93 ,
Could you describe what's your expected result with an image? I could not get your meaning clearly, do you want to remove the ^ or any other requirement?
jn93 671 Reputation points

2022-10-20T02:04:52.36+00:00

Hi @ZoeHui-MSFT , I would like to get derived column for UKEY. For example, from the data below, I want to get the 91TMIQUO6123265 only from the left. In excel, we can use text before delimiter. How can we achieve using SSIS and also SQL.

Accepted answer

1 additional answer

Your answer

ZoeHui-MSFT 41,491 Reputation points

2022-10-20T01:29:09.57+00:00

Hi @jn93 ,
Could you describe what's your expected result with an image? I could not get your meaning clearly, do you want to remove the ^ or any other requirement?
jn93 671 Reputation points

2022-10-20T02:04:52.36+00:00

Hi @ZoeHui-MSFT , I would like to get derived column for UKEY. For example, from the data below, I want to get the 91TMIQUO6123265 only from the left. In excel, we can use text before delimiter. How can we achieve using SSIS and also SQL.

Answer 1

ZoeHui-MSFT 41,491

Hi @jn93 ,

If the length for ukey is fixed as 15, you may also use left function in SSIS.

LEFT(ukey,15)

If the length for ukey is not fixed, you may use token function in SSIS.

TOKEN(ukey,"^",1)

If you want to do that with T-SQL code, try with below.

declare @ukey as nvarchar(max)='91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265'  
select left(@ukey, charindex('^', @ukey + '^') - 1)  
  
or  
declare @ukey as nvarchar(max)='91TMIQUO6123265^91TMIQUO6123265^91TMIQUO6123265'  
select left(@ukey, 15)

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

jn93 671 Reputation points

2022-10-20T03:29:51.903+00:00

Hi @ZoeHui-MSFT , your method is working great as per shown below. But for the update, how can I join on this derived column as per shown in sql script below? Sorry I'm not really good in sql.
jn93 671 Reputation points

2022-10-20T03:31:52.54+00:00

Please refer more detail diagram below for the update.
jn93 671 Reputation points

2022-10-20T04:22:41.417+00:00

Hi @ZoeHui-MSFT , I managed to write the update sql syntax like shown below. But seems like, there is another enhancement need to for below syntax. This is because, when there is no special character "^" in UKEY, it is throw me error with below
jn93 671 Reputation points

2022-10-20T04:46:28.663+00:00

Hi @@ZoeHui-MSFT ,
When compare yours, sorry I missed out to add some more in my syntax. Now the issue solved. Thanks and Regards.
ZoeHui-MSFT 41,491 Reputation points

2022-10-20T05:23:02.22+00:00

Good job! :)
jn93 671 Reputation points

2022-10-20T05:48:04.673+00:00

Thanks

Answer 2

jn93 671

Hi @ZoeHui-MSFT ,
Not sure I should posted this another question or not for this. Please let me know if I should post this as another question. I will ask here because question I ask before doesn't fulfil the requirement :'(. Based on the sample file with ^ character, I have to get the desired output in table database like below. How can we achieve this using SSIS? Kindly please help.

ZoeHui-MSFT 41,491 Reputation points

2022-10-20T08:51:49.423+00:00

Hi @jn93 ,
It is hard to do that via SSIS, it could be reached by TSQL code. If you need, then I will help with tsql code.
jn93 671 Reputation points

2022-10-20T09:11:35.853+00:00

Hi @ZoeHui-MSFT , yes can with tsql code. But my source file is txt file.

ZoeHui-MSFT 41,491

Hi @jn93 ,
Please first use SSIS to load the txt file to the sql database table and then use below tsql.

DECLARE @separator CHAR(1) = '^';  
      
;WITH rs AS  
(  
      SELECT   
           s = '["' + REPLACE(autonumm, @separator, '","') + '"]',  
           s1 = '["' + REPLACE(ukey, @separator, '","') + '"]',  
		   s2 = '["' + REPLACE(inscode, @separator, '","') + '"]',  
		   s3 = '["' + REPLACE(actiontimestamp, @separator, '","') + '"]'  
  
      FROM test1020  
  )  
  SELECT autonumm.[value] AS autonumm, ukey.[value] AS ukey,inscode.[value] AS inscode,actiontimestamp.[value] AS actiontimestamp  
  FROM rs  
     CROSS APPLY OPENJSON (s, N'$') AS autonumm  
     CROSS APPLY OPENJSON (s1, N'$') AS ukey  
	  CROSS APPLY OPENJSON (s2, N'$') AS inscode  
	  CROSS APPLY OPENJSON (s3, N'$') AS actiontimestamp   
 WHERE autonumm.[key] = ukey.[key] and autonumm.[key]=inscode.[key] and autonumm.[key]=actiontimestamp.[key]

Note that OPENJSON is available under compatibility level 130. You may try to execute before using.

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130

jn93 671 Reputation points

2022-10-20T09:26:40.843+00:00

Hi @ZoeHui-MSFT , It is throw me error when I alter database :'(. Any other alternative solution?
ZoeHui-MSFT 41,491 Reputation points

2022-10-20T09:39:04.103+00:00

Hi @jn93 ,
It seems that your version is lower than SQL Server 2016. Another way is using string_split which is also support only SQL Server2016 and later.
You may post a new question with tag of sql-server-transact-sql to look for more professional advice from the experts.
Remember to give the sample data and your sql server version. :')
jn93 671 Reputation points

2022-10-20T09:43:07.11+00:00

Hi @ZoeHui-MSFT , Thanks for the advice. Will do. :)

Share via

How to add text before delimiter ^ in SSIS from CSV file

1 additional answer

Your answer