SSIS Derived column

Posted on 2011-03-10
Medium Priority
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
Question by:MoreThanDoubled
LVL 39

Accepted Solution

Pratima Pharande earned 2000 total points
ID: 35094247
try this

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

Author Comment

ID: 35094842

Thanks pratima mcs :)

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

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

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

586 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