Stack Overflow
Stack Overflow

Questions tagged [derived-column]

SSIS Derived Column Transformation is one of the SQL Server Integration Services that can be added within a Data Flow Task, it is used to add a new column to the data pipeline by applying SSIS expressions. The developer can choose whether to add a new derived column or to replace an existing column.

SSIS Derived Column Transformation is one of the SQL Server Integration Services that can be added within a Data Flow Task, it is used to add a new column to the data pipeline by applying SSIS expressions. The developer can choose whether to add a new derived column or to replace an existing column.

SSIS expressions are a combination of literals, functions, operators that yields a single data value. An expression can be composed of a single value (“abc”) or a variable (@[User::Variable1]) or a function (GETDATE()), or it can be more complex and contains some conditionals (CASE statement) or some mathematical operations or some string manipulation.

References

164 questions
2
votes
2 answers

ssis substring charindex , split forename and surname

I have an Excel source in SSIS with a column called [Name] which has an entry like Joe Bloggs I want to separate [Name] into [Forename] and [Surname] How can I make two derived columns please as it does not work as an SQL statement As normal SQL…
Joeysonic
  • 255
  • 3
  • 11
2
votes
1 answer

SSIS Derived column - Type casting issue

I am very new to SSIS, I have existing SQL table and I want to load data into decimal(10,2) column, but .csv file as source file contains -- value in it so that I am trying to replace -- value to 0.00 using derived column and expression I have…
Raj
  • 301
  • 4
  • 12
2
votes
1 answer

Extract middle name using SSIS derived column

(SSIS topic) I have a Full Name column containing names with middle initial and names without middle initial. How can i get the middle initial and put it in the Middle Initial column. How can I solve this using SSIS derived column. |Middle Initial …
datanewbie
  • 29
  • 3
2
votes
1 answer

SSIS get only Year of a derived column that is a datetime

I need to make the FORM_YEAR the year of a derived column. The derived column is called Invoice_Document_Date and its data type is DateTime so I only want to grab the year. For my expression I wrote YEAR(dc_Invoice_Document_Date) but that doesn't…
Abbie Waters
  • 21
  • 2
2
votes
1 answer

SSIS Derived Column to add first 3 values of current year (YYY) to a string date (MMDDY)

I have a string date value that is missing the 201 of 2019. The string value 03189101545 which should be 03-18-(201)9 10:15:45 I did a left from a serial number variable to extract 03189 and can't quite figure out how to add back the full…
Neil B.
  • 63
  • 7
2
votes
2 answers

Nested IF ELSE in a derived column

I have the following logic to store the date in BI_StartDate as below: If UpdatedDate is not null then BI_StartDate=UpddatedDate ELSE BI_StartDate takes EntryDate value , if the EntryDate is null then BI_StartDate=CreatedDate If the CreatedDate IS…
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
2
votes
2 answers

Retrieve text after last back slash using SSIS Derived Column transformation

Using SQL Server 2014. I have a field that contains a string that contains a full file path i.e. \\Server\Folder1\Folder2\Folder3\File21.csv I only want what is after the last backslash i.e. File21.csv So in the world of SQL I would use: Select…
Michael
  • 2,507
  • 8
  • 35
  • 71
2
votes
1 answer

SSIS Derived Column From CSV (Cast String to Float)

I got a CSV like below. The column got some empty rows and the CSV gives the values as a string. But I want to make it a float like ‘3.00000’ with 5 decimals. First, I need to replace the ‘,’ symbol and replace the null values I used this derived…
Aercheon
  • 101
  • 5
2
votes
1 answer

Importing sensitive data from SQL server to a flat file

I am new to SSIS and I want to import data from the SQL database to a flat-file. I have a column name named SecurityNumber which contains sensitive data, so using a derived column I want to use a symbol * to hide some numbers. For example, if the…
hana
  • 101
  • 10
2
votes
1 answer

SSIS : Conversion text stream DT_TEXT to DT_WSTR

I am creating a package in SSIS, and want to convert a file with one large column into multiple columns. I have a table containing several rows with a single column of raw data. The data was copied from a notepad file, and each row contains pipe…
Sast77
  • 97
  • 1
  • 9
2
votes
1 answer

How can I concatenate yyyyMMdd at the end of my txt file in ssis?

I am using SSIS to transform my data. My file_name is test_heathcare.csv. How can I bring it to my flat file destination as new derived column being filename as test_healthcare20190712.txt? If anyone know please help me .
Kate
  • 445
  • 3
  • 9
  • 22
2
votes
1 answer

Calculate age using DOB column

I have dob column how can i calculate age by using DOB column in derived column ? I tried using as below but this throws an error . datediff(yy, Dob,getdate())
Kate
  • 445
  • 3
  • 9
  • 22
2
votes
1 answer

Last Date of Year in SSIS Derived Column as a Date type

I am creating a derived column in SSIS to pull last date of year for 2012. What would the expression look like? LastDateofYear attribute in date data type
Starbrite420
  • 21
  • 2
2
votes
1 answer

Reading derived column expression

I am in need of assistance reading this confusing derived column expression which contains multiple Boolean expressions. I've tried reading it multiple ways, but not sure which one is correct. ISNULL(ContractNumber) ? (ISNULL(PaidLossAmount) &&…
daniness
  • 363
  • 1
  • 4
  • 21
2
votes
1 answer

remove special characters " and ' from a column using SSIS

I have a csv which has a column called "Value" and in it there are a combination of strings & special characters. Eg: Value abc xyz " pqr ' I want to replace the " and ' special characters with empty string "" in the output file.I used the derived…
AtuD
  • 83
  • 2
  • 11
1 2
3
…
10 11