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.
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
ServiceTypeDescriptionUnitIDSubscriberAccountActivityDateCommentQuantityPrice
SLE-SVC-AP3AT&T PLUS UNLIMITED SVC PACK 33450293. .5/12/20143450293/. ./2014-05-1217.95
SLE-SVC-AP3AT&T PLUS UNLIMITED SVC PACK 33314674. Pectra brothers Inc.1RR870810/22/20133314674/. Pecora brothers Inc./2013-10-2217.95
SLE-TOLLCALLSLE TOLL CALL REPORT(EACH)3314674. Pectra brothers Inc.1RR8708SLE-SVC-AP33314674/. Pecora brothers Inc./SLE-SVC-AP310.1
SLE-SVC-AP3AT&T PLUS UNLIMITED SVC PACK 33314258ADAM WEIGOLD1RR44137/9/20143314258/ADAM WEIGOLD/2014-07-0917.95
SLE-TOLLCALLSLE TOLL CALL REPORT(EACH)3314258ADAM WEIGOLD1RR4413SLE-SVC-AP33314258/ADAM WEIGOLD/SLE-SVC-AP330.3
SLE-SVC-AP3AT&T PLUS UNLIMITED SVC PACK 33419412Andreas WINHAD1RR774712/11/20133419412/Andreas Woppman/2013-12-1117.95
SLE-TOLLCALLSLE TOLL CALL REPORT(EACH)3419412Andreas Andreas WINHAD1RR7747SLE-SVC-AP33419412/Andreas Woppman/SLE-SVC-AP3313.1
SLE-SVC-AP3AT&T PLUS UNLIMITED SVC PACK 33418422andrew hebb1426595/16/20143418422/andrew hebb/2014-05-1617.95
SLE-TOLLCALLSLE TOLL CALL REPORT(EACH)3418422andrew hebb142659SLE-SVC-AP33418422/andrew hebb/SLE-SVC-AP370.7
SLE-SVC-T2SLE TMOB UNL SIG MTLY TST (MO)3115123Anthony Corsetti160010619/17/20123115123/Anthony Corsetti/2012-09-1715.95
SLE-SVC-AP3AT&T PLUS UNLIMITED SVC PACK 33420559ARTHUR HAPPY1426762/11/20143420559/ARTHUR HOLMES/2014-02-1117.95
SLE-TOLLCALLSLE TOLL CALL REPORT(EACH)3420559ARTHUR HAPPY142676SLE-SVC-AP33420559/ARTHUR HOLMES/SLE-SVC-AP350.5
SLE-SVC-T2SLE TMOB UNL SIG MTLY TST (MO)3057993August Graemiger160010296/14/20123057993/August Graemiger/2012-06-1415.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