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
1 answer

Convert mmddyy to YYYY-MM-DD in expression task in SSIS

I'm trying to use the below code to convert data from '010118' to '2018-01-01' (DT_DATE)(RIGHT(DATE,2) + LEFT(DATE,2) + SUBSTRING(DATE,3,2)) When I run this in SSIS i'm getting conversion error An error occurred while attempting to perform a…
Gowtham Ramamoorthy
  • 896
  • 4
  • 15
  • 36
2
votes
2 answers

I want to convert DATEADD("d",- (DAY(GETDATE())) + 1,GETDATE()) to YYYY-MM-DD format

The expresssion DATEADD("d",- (DAY(GETDATE())) + 1,GETDATE()) is correct and its giving me 2/1/2019 3:45:02 PM But i want my answer to be as 2019-02-01 in SSIS PLease help me
vani malge
  • 85
  • 1
  • 6
2
votes
1 answer

SISS - Check Null DerivedColumn

Does someone knows how to check IS Null expression on SISS ? IF columns is NULL > RETURN NULL , IF is NOT return value from column I am developing this on DerivedColumn. FYI: The column is not always in my Excel layout, that is the reason that I…
Luiz
  • 141
  • 2
  • 14
2
votes
2 answers

Derived column failures due to error codes DTS_E_INDUCEDTRANSFORMFAILUREONERROR 0xC0049067 and DTS_E_PROCESSINPUTFAILED 0xC0209029

I've been working on an SSIS package for which I've added derived columns (which I've posted earlier on here). The issue I'm running into is that this child package is running fine in one environment, but for some reason, is failing in my Dev…
daniness
  • 363
  • 1
  • 4
  • 21
2
votes
3 answers

REPLACE NULL IN SSIS Derived Column

I have an SSIS package which transfers data from SAS to SQL Server. I'm creating a derived column but cannot get the REPLACENULL feature to work. I am receiving the error "Invalid character value for cast specification" which I am sure is…
jerry
  • 129
  • 1
  • 3
  • 10
2
votes
1 answer

SSIS Date/Numeric Conversion from Flatfile

I have a Flat file which contains 2 columns . One is date time in this format 2017-11-06T11:16:08 AM GMT and Another Column Total value stored in this format £39.00. Destination is OLEDB (ms sql), Column 1 is Datetime and column 2 is Int in the…
Ven
  • 2,011
  • 1
  • 13
  • 27
2
votes
2 answers

Convert month name to month number in SSIS

I have an input column "MonthName" which has values in the following string format 22-MAY-2017 02:29:33.00. I would like to convert this into Datetime data type in the destination table. For that the following conversion needs to be done…
Dee
  • 111
  • 2
  • 9
2
votes
2 answers

SSIS - Derived Column - Substring/Charindex - Case when statement

I've this SQL Code: Select [Name] = case when CHARINDEX(''.'', [Name])>0 then LEFT([Name],CHARINDEX(''.'', [Name])-1) else [Name] end, [System] = case when reverse(SUBSTRING(REVERSE(…
Pedro Alves
  • 1,004
  • 1
  • 21
  • 47
2
votes
1 answer

Create Null-able numeric column in Derived column component

I use SSIS in SQL Server 2016. I have to create a new column in my DataFlow. I use DrivedColumn component to create this new column. In this case I need a nullable numeric column like below image : As you can see, I have error in this case. How can…
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144
1
vote
3 answers

Case expressions in derived column for SSIS

How do I modify the column having 2,4,5 as values to 2=active, 4=closd, and 5=inactv in SSIS derived column? (SQL Server DB) I'm expecting the column should show values. 'active' instead of '2' 'closd' instead of '4' 'inactv' instead of '5'
Dheeraj
  • 19
  • 4
1
vote
1 answer

ADF - Change the date format from any format coming from csv to yyyy-MM-dd HH:mm:ss while loading in target sql table taking datetime

I have a situation where I am getting dates in two separate formats, MM/dd/yyyy & yyyy-dd-MM, AND there might be even more different formats as well in csv which will be obviously in string. Below are the data which currently come as String from…
thesacredkiller
  • 13
  • 4
1
vote
0 answers

SSIS Accept both CRLF & LF formats

I am trying to modify my SSIS package to accept both CRLF and LF row terminated files. I have come across the suggestion to use a derived column where the you REPLACE(ColumnName,"\r","") like to so to remove the CR leaving just the LF at the end…
dcfretwell
  • 43
  • 5
1
vote
1 answer

Converting data in column in SSIS

I'm writing an SSIS package to load data from a .csv into a db. There's a column in the csv file that is supposed to have a count, but the records sometimes have text, so I can't just load the data in as an integer. It looks something like this: I…
Seth
  • 33
  • 3
1
vote
2 answers

SSIS convert string with no 0's to date format

I'm working trying to convert a string data type to a date that does not contain 0's in the string of the month and day part and need to convert it into a date format yyyy-MM-dd, anything that is a single digit will come without a zero For example,…
HappyCoder123
  • 67
  • 4
1
vote
1 answer

Derived column mapping fails

I am mapping data between my oracle table to the Azure SQL DB table. I need to map the name field from the Source which is like below Name AX1 BD A1 12 BC W1 In to source, I need to just map the characters that are before the space…
user4912134
  • 1,003
  • 5
  • 18
  • 47
1 2 3
…
10 11