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

How to achieve TRY_CONVERT functionality in SSIS?

In SSIS package I have derived column in which I want to format phone like below: CASE WHEN TRY_CONVERT(BIGINT, phone) IS NULL THEN NULL ELSE phone END How can I use the SSIS expression to achieve same result as above?
Jay Desai
  • 821
  • 3
  • 15
  • 42
5
votes
1 answer

wrong Dates values in ssis

I am working with ssis. I have a column that contains date in format integer YYYYMMDD: For example to day I get 20190214.I am using derived column to convert it in format date YYYY-MM-DD in my case for example 2019-02-14 But sometimes I receive…
test test
  • 83
  • 11
4
votes
2 answers

ssis derived column - check if date field is null

In SSIS derived column I need to check if a Date field is empty or null, I'm using this expression but I got an error: TRIM([date field]) == "" ? NULL(DT_DATE) : TRIM([date field]) The field is of DT_DATE data type. What's wrong in that? I've used…
marko
  • 487
  • 1
  • 6
  • 15
4
votes
2 answers

Can we cast from string or char datatype to int (or numeric) in SSIS

I find this little challenging. I have source table and destination table. Source table has one of the column with char datatype with length of 2. Destination table has one of the column with int datatype. Task is to migrate data from source to…
Binoy
  • 390
  • 5
  • 19
4
votes
2 answers

SSIS Derive timestamp column into date and time columns

I have this Timestr in varchar 28/12/2016 16:39:01 How to Derived the column into date and time in SSIS Desired result Date 2016-12-28 in Date format Time 16:39:01 in time format This is what i have in SQL so far SELECT…
Jason312
  • 197
  • 1
  • 1
  • 10
4
votes
2 answers

SSIS - Derived Columns

I have one small question, I am new so please help me. In my project I have one text file which has zip4 and zip5 , I want to concatenate both the zip code but it has one problem, at some place zip4 is blank (not null) so when I used the derived…
343
  • 295
  • 2
  • 8
  • 22
3
votes
1 answer

SSIS Derived Column Expression to skip null/blank value and take the next digits of string value

I am very new to SSIS and I am performing a task where I have to take the flat-file text document and then derive the columns with specific digit lengths. For eg, lines in the text document are: 101001A00000000000000309493020111139 …
sarcastic reality
  • 41
  • 1
3
votes
1 answer

OLE DB Destination: Invalid character value for cast specification

My table source: num_facture TYPE actif date 1 1 1 2010-01-31 00:00:00.000 2 2 1 2011-01-31 00:00:00.000 3 3 2 2012-01-31 00:00:00.000 4 4 2 2013-01-31 00:00:00.000 The Column data types are: Column Data…
chaneb
  • 67
  • 1
  • 6
3
votes
4 answers

Separate data with substring and findstring

I have a problem I think it's something simple but I'm just getting started on this, I have a .txt file that contains Kayle;Osvorn;35;4399900433 What would be these my columns: First name;Last name;Age;Phone I need to separate them through the…
Jorge
  • 485
  • 4
  • 15
3
votes
1 answer

SSIS derived column remove text from string, keep only numeric values

I am trying to remove text from a specific column and only keep the numeric values. I tried to use a derived column and also tried to search for the answer, but did not find the solution. For example, the table looks like…
user7418776
  • 57
  • 7
3
votes
1 answer

Why expression of Derived Column component don't working with empty value in SSIS?

Can someone advice me please? I have table in SQL Server create table t_test_date ( tcol_date date, tcol_varchar varchar(20), tcol_date1 date ) When I try insert data from flat file with…
Aunakul
  • 51
  • 5
3
votes
1 answer

SSIS How to Map Hundreds of Derived Columns

I have a table that needs to map 104 columns and convert from Unicode to non-Unicode, etc as (DT_STR,10,1252)[ColumnName] within the derived column editor in SSIS. I cannot simply CAST from the SQL statement given I am ingesting from CDC Control…
Sauron
  • 6,399
  • 14
  • 71
  • 136
3
votes
2 answers

Derived column Transformation Editor - I need add number at the end of order number in increment order

I have a csv file having many lines with different order number I need to change them via SSIS Derived column Transformation Editor so I can have transformed output. I need to write Expression that adds number at the end of order but I need…
David
  • 67
  • 1
  • 6
3
votes
2 answers

SSIS · Trigonometric functions

I'm trying to calculate SIN and COS in SSIS expression (as new column), but I cannot find any expression. For example: SIN(lat_org_rad) COS(long_org_rad) ACOS(long_org_rad) Could anybody help me? Thanks
user3868641
  • 162
  • 2
  • 13
3
votes
1 answer

SSIS Derived Column Expression to display blank based on the value of another field in a separate OLE DB Source

I have two different OLE DB Sources from different servers merging into a merge join. I am thinking that I need to use a derived column to put the value of one field in one of the OLE DB Sources as empty based on the value of a field in the other…
danielsvic
  • 59
  • 6
1
2 3
…
10 11