56196

SQL script to SSIS expression

I have the below T-SQL line of query that I'm trying to translate into Visual studio SSIS expression into derived column task.

So tableA has just [Work item /Submission no#] column, but I need to split them into two column like SubmissionCommon and SubmissionNumber in TableB when below case is executed.

CASE WHEN ISNUMERIC(SUBSTRING([Work item /Submission no#], 4, 2)) = 1 THEN LEFT([Work item /Submission no#], 15) ELSE LEFT([Work item /Submission no#], 16) END AS SubmissionCommon, [Work item /Submission no#] AS SubmissionNumber

Answer1:

Solution

I will suggested that you first add a derived column (you can name it IsNumeric), with the following expression:

(DT_NUMERIC, 18, 2)SUBSTRING([Work item /Submission no#], 4, 2) == (DT_NUMERIC, 18, 2)SUBSTRING([Work item /Submission no#], 4, 2) ? 1 : 0

Then near the bottom of the Derived Column Transform Editor window, click Configure Error Output. You need to tell SSIS to <strong>Ignore failure on Error</strong>

Add another Derived Column connect to the first on with the following expression

REPLACENULL([IsNumeric],0) == 1 ? LEFT([Work item /Submission no#], 15) : LEFT([Work item /Submission no#], 16)

Because the first one may throws an error

For detailed informations just follow this article:

Recommend

  • Validating numeric formats in Win Forms
  • Find mean of an array with both numbers and strings with Matlab
  • Query using a derived table with ISNUMERIC results in conversion failure (varchar to int)
  • Detecting cells in column that cause error in SQL
  • How do I embed a custom user-control in MS Word?
  • Implicit property animations do not work with CAReplicatorLayer?
  • Change the width of the JQM panels
  • gulp.watch running same task multiple times when saving many files
  • What's a fast (non-loop) way to apply a dict to a ndarray (meaning use elements as keys and rep
  • CKeditor stripping font tags instead of converting to span
  • How can I add div content to a new page in jspdf?
  • Criterion causing memory consumption to explode, no CAFs in sight
  • How do I retrieve the user information of a user authenticated with Apache's mod_ldap?
  • How do I configure context broker accept post requests from my remote sensor?
  • Groovy: Unexpected token “:”
  • How to define and use opencv mat of user type
  • Replace value with Factor in r data.table
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Alert pop up with LWUIT
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Disabling Alt-F4 on a Win Forms NotifyIcon
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • Array.prototype.includes - not transformed with babel
  • swift auto completion not working in Xcode6-Beta
  • jquery mobile loadPage not working
  • Data Validation Drop Down Box Arrow Disappearing
  • AngularJs get employee from factory
  • log4net write single file for each call to log.info
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • Angular 2 constructor injection vs direct access
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • UserPrincipal.Current returns apppool on IIS