November 10, 2014 at 10:51 am
Hi I have a bit of a dilemma I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...
I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?
Exmple--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3
Description code
Test1 1
Test2 2
Test3 3
November 10, 2014 at 11:12 am
cbrammer1219 (11/10/2014)
Hi I have a bit of a dilemma I am wanting to creating a column based on a value in a flatfile, and insert that value into the destination table...I have a flatfile and a oledb destination, I dropped a script component on the dataflow tab, and I am wanting to take an existing field and create a for lack of a better word, "CODE" field based on that value. How would I accomplish this?
Exmple--- where description = Test1 code = 1, Test2 code = 2, and same thing for Test3
Description code
Test1 1
Test2 2
Test3 3
Is this as simple as merely taking character 5, or is there more to it than that?
Before resorting to a Script Component, you should check out the Derived Column transformation, it is simpler, faster and may have enough horsepower to get what you want.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 10, 2014 at 11:29 am
No there is a field that has multiple values, depending on these values I want to put a integer in a field "[ChargeType_Code]", I am reading a csv and inserting these values from the file, but there is no "CODE", I am using the description to generate the "[ChargeType_Code]" then insert it into the table I will be reporting off.
USE [AA_Helper]
GO
/****** Object: Table [dbo].[tblNapco] Script Date: 11/10/2014 1:21:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblNapco](
[ServiceType] [text] NULL,
[Description] [text] NULL,
[ChargeType_Code] [text] NULL,
[UnitID] [text] NULL,
[Subscriber] [text] NULL,
[ActivityDate] [text] NULL,
[Comments] [text] NULL,
[Qty] [text] NULL,
[Price] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [AA_Helper]
GO
/****** Object: Table [dbo].[tbl3rdPartyRecon] Script Date: 11/10/2014 11:08:38 AM ******/
DROP TABLE [dbo].[tbl3rdPartyRecon]
GO
/****** Object: Table [dbo].[tbl3rdPartyRecon] Script Date: 11/10/2014 11:08:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl3rdPartyRecon](
[CustName] [nvarchar](200) NULL,
[VendorName] [nvarchar](200) NULL,
[Cust_ID] [nvarchar](200) NULL,
[ChargeType] [varchar](200) NULL,
[ChargeType_Code] int,
[ChargeDesc] [varchar](200) NULL,
[FileDate] [datetime] NULL,
[RateType] [varchar](200) NULL,
[Amount] [varchar](200) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SAMPLE DATA
ServiceType Description UnitID Subscriber Account ActivityDate Comment Quantity Price
SLE-SVC-AP3 AT&T PLUS UNLIMITED SVC PACK 3 3450293 . . 5/12/2014 3450293/. ./2014-05-12 1 7.95
SLE-SVC-AP3 AT&T PLUS UNLIMITED SVC PACK 3 3314674 . Pectra brothers Inc. 1RR8708 10/22/2013 3314674/. Pecora brothers Inc./2013-10-22 1 7.95
SLE-TOLLCALL SLE TOLL CALL REPORT(EACH) 3314674 . Pectra brothers Inc. 1RR8708 SLE-SVC-AP3 3314674/. Pecora brothers Inc./SLE-SVC-AP3 1 0.1
SLE-SVC-AP3 AT&T PLUS UNLIMITED SVC PACK 3 3314258 ADAM WEIGOLD 1RR4413 7/9/2014 3314258/ADAM WEIGOLD/2014-07-09 1 7.95
SLE-TOLLCALL SLE TOLL CALL REPORT(EACH) 3314258 ADAM WEIGOLD 1RR4413 SLE-SVC-AP3 3314258/ADAM WEIGOLD/SLE-SVC-AP3 3 0.3
SLE-SVC-AP3 AT&T PLUS UNLIMITED SVC PACK 3 3419412 Andreas WINHAD 1RR7747 12/11/2013 3419412/Andreas Woppman/2013-12-11 1 7.95
SLE-TOLLCALL SLE TOLL CALL REPORT(EACH) 3419412 Andreas Andreas WINHAD 1RR7747 SLE-SVC-AP3 3419412/Andreas Woppman/SLE-SVC-AP3 31 3.1
SLE-SVC-AP3 AT&T PLUS UNLIMITED SVC PACK 3 3418422 andrew hebb 142659 5/16/2014 3418422/andrew hebb/2014-05-16 1 7.95
SLE-TOLLCALL SLE TOLL CALL REPORT(EACH) 3418422 andrew hebb 142659 SLE-SVC-AP3 3418422/andrew hebb/SLE-SVC-AP3 7 0.7
SLE-SVC-T2 SLE TMOB UNL SIG MTLY TST (MO) 3115123 Anthony Corsetti 16001061 9/17/2012 3115123/Anthony Corsetti/2012-09-17 1 5.95
SLE-SVC-AP3 AT&T PLUS UNLIMITED SVC PACK 3 3420559 ARTHUR HAPPY 142676 2/11/2014 3420559/ARTHUR HOLMES/2014-02-11 1 7.95
SLE-TOLLCALL SLE TOLL CALL REPORT(EACH) 3420559 ARTHUR HAPPY 142676 SLE-SVC-AP3 3420559/ARTHUR HOLMES/SLE-SVC-AP3 5 0.5
SLE-SVC-T2 SLE TMOB UNL SIG MTLY TST (MO) 3057993 August Graemiger 16001029 6/14/2012 3057993/August Graemiger/2012-06-14 1 5.95
ChargeType_Code
Partial Mont = 4
Fire 5 Min Supervison DLR3G = 4
Internet device with Daily supervision = 4
Advanced Protection Logic (APL) using GSM radio = 4
410 + SMS UN = 4
iGSM device with Monthly supervision = 4
Activation Fee = 1
Activation F = 1
External Activation Fee = 1
Sensor Activ = 1
AlarmNet-A Inter-network Communication Charges Per Message = 3
Additional Messages or Data Usage for GSM/iGSM with Daily or Unsupervised Service = 3
Additional Messages or Data Usage for GSM/iGSM with Monthly Supervision = 2
November 10, 2014 at 11:54 am
Phil is correct. You should be able to do this in a Derived Column transformation. The expressions are quite powerful.
Gerald Britton, Pluralsight courses
November 10, 2014 at 11:56 am
I am looking into this, but new to this and don't really understand, how to write the expression, isn't like a case in TSQL..
November 10, 2014 at 12:06 pm
Here's a few links that might be helpful:
Integration Services (SSIS) Expressions
http://msdn.microsoft.com/en-us/library/ms137547%28v=sql.110%29.aspx
SSIS Expression Cheat Sheet
http://pragmaticworks.com/Training/Resources/Cheat-Sheets/SSIS-Expression-Cheat-Sheet
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
November 10, 2014 at 12:13 pm
Not sure I understand the intricacies of what you're doing, I'm a little lost in your explanation, but if it's a 1:1 from Description to code, a Lookup Component with ignore errors (NULLS for unmatched then) should do what you need.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply