Solved

SSIS Derived column

Posted on 2011-03-10
2
1,096 Views
Last Modified: 2012-05-11
I have a stored procedure that has a companyId field formatted as follows:

Select CONVERT(VARCHAR(14),c.company_id) as CompanyId from table1

I am creating a flat file in SSIS and the companyid field needs to
be right justified, I.e. '      123456' and the maximum length of the field should be 14 spaces on the flat file.
In the SSIS Derived Column Transformation Editor i wrote the syntax:
REPLICATE(" ",14 - LEN(TRIM(CompanyId)))  in the expression field.  I thought that this would give me what i needed but it does not.


Can someone help me with the format.
I am using MS SQL Server 2008
0
Comment
Question by:MoreThanDoubled
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 35094247
try this

REPLICATE(" ",14 - LEN(TRIM(CompanyId)) + CompanyId
0
 

Author Comment

by:MoreThanDoubled
ID: 35094842

Thanks pratima mcs :)

I added a trim after companyId ..it works great thanks.

REPLICATE(" ",14 - LEN(TRIM(CompanyId)) + TRIM(CompanyId)
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question