Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

SSIS Edit Flat Flie Source

Is there a way to edit a FIXED flat file source, I need to trim 14 characters to the left before it goes to the destination.
0
decoded
Asked:
decoded
  • 6
  • 6
  • 2
1 Solution
 
mikhoCommented:
If you are familiar with .Net the easiest way to do this would be to create a "Transformation" script component and use code to strip the characters from the file .
0
 
decodedAuthor Commented:
Not really...would you give me some help.
0
 
mikhoCommented:
I dont code .Net myself :) , I only know its possible to do...

You could try to add a column with the 14 chars that you want to remove...
I think its in under the advanced settings on the task...

I'm not on my developer computer now so I cant check
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
decodedAuthor Commented:
i dont think that would work. that would change the rows
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
Can you show an example of what you need to do, like a source and expected results? I don't get what do you say by FIXED flat file source, I need to trim 14 characters to the left before it goes to the destination.

Thanks
Jason
0
 
decodedAuthor Commented:
Attahed is what I mean by fixed width.

but what i am try to remove is the first 14 characters of a file:

"date=20120213" 12364 xxxxx971 898  2645   567wr

in quotes the first 14 characters.

fixed file
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Oh, OK...so what's the problem then? just ignore the first 14 chars when you insert into your final destination.

or you can even read each row as ONE column, then use  a derived column to remove the first 14 chars

see attached
result.jpg
DCT.jpg
0
 
decodedAuthor Commented:
The problem is that I need the first 14 characters of the first row only.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
oh I thought the opposite!!

then use expression substring(column, 1,14)  and that will get you the first 14 chars.
0
 
decodedAuthor Commented:
This works however, its remove characters for all the rows.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
isn't that what you need ? or what's your actual requirement?
0
 
decodedAuthor Commented:
attached is what i need to remove.

what need to be erase
i just need to remove whats highlighted then import into another text file.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
OK, I think we could add a script task to create a row number and in the derived column we could use:     rownum ==1 ? substring(column,14,len(column)) : column

use the same example here: http://asqlb.blogspot.com/2011/05/ssis-selective-rows-count-using-script.html to create the counter

let me know if you need more help.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
how is this working out for you? any questions?

If you're stuck, send me a sample data file and I'll create a sample package for you...

regards,
Jason
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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