• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

SSIS - Remove portion of field on text file import.

Below is a portion of a text file that I am trying to import to a SQL table from a comma delimited file.  I have two questions:

1) How do I get rid of the all of the text that prefixes each data value?  For example how do I strip off the 'cat=' and the 'cat_desc' when I import?
2) How can I import the second column as a single column, instead of having SSIS break the field at each comma embedded in the string?

cat=52,cat_desc="Desc01, Desc02",hostname=www.somehost.com
cat=34,cat_desc="Desc06, Desc03, Desc10",hostname=www.somehost.com
cat=52,cat_desc="Desc01, Desc02",hostname=www.somehost.com
cat=64,cat_desc="Desc64",hostname=www.somehost.com

Thanks for your help!
Chris
0
TriCoreIT
Asked:
TriCoreIT
  • 2
1 Solution
 
gothamiteCommented:
Can you run the following powershell snippet on the input file before it gets ingested to SSIS?
# pre-format data
$data=get-content data.txt
$out = @()
foreach ($line in $data)
{
    $out+=$line.Replace("cat=","`"").Replace(",cat_desc=","`",`"").Replace(",hostname=","`",`"").Replace("`"`"","`"")+"`""
}
$out | out-file data_out.txt
$out

Open in new window

0
 
TriCoreITAuthor Commented:
Thanks for the repsonse gothamite.  I was hoping to avoid a step outside of the SSIS environment, but will have to resort to this "pre-processing" if it cannot be done within SSIS.

Chris
0
 
Reza RadConsultant, TrainerCommented:
you can do it with SSIS,
just you need a Script Component Transformation after your flat file source.

0
 
TriCoreITAuthor Commented:
a little more direction in terms of how to write the script would have been good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now