Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [ssis-expressions]

16 questions
6
votes
3 answers

SSIS 2012 VS_ISBROKEN on Derived Column

I have converted a SSIS package built in VS2008 to open in VS2010. I have a Derived Column component that fails validation. It works fine in VS2008. Here is the Expression used in Derived Column... FINDSTRING( SecurityDescription,"\n",1) > 0 …
K09
  • 1,454
  • 13
  • 39
  • 61
4
votes
1 answer

How do I map SSIS 2012 Package Variables to Parameters?

I have created multiple packages that use variables to assign Connection Manager Expressions such as Connection String. However in the new SQL 2012 Project Deployment Model I don't seem to be able to configure these variables. Would I therefore…
PeterX
  • 177
  • 1
  • 3
  • 10
2
votes
1 answer

convert string allow null col to int?

I want to convert string to int my string type [DT_WSTR] and my expression ISNULL(col) || TRIM(col) == "" ? (DT_I4)0 : (DT_I4)col I get this error [Derived Column [518]] Error: The conditional operation failed.
NinjaDeveloper
  • 245
  • 1
  • 6
  • 17
2
votes
1 answer

ssis convert ddmonyyyy dd/mm/yyyy

Hi guys trying to convert 16JAN2014 --> 16/01/2014. Any ideas??? i'm trying to avoid the horrendous expression of ? JAN : 1 ? FEB : 2 etc. etc. SSMS will quite happily do SELECT MONTH('16JAN2014') to get me the number of month however MONTH(…
jhowe
  • 271
  • 1
  • 6
  • 19
2
votes
1 answer

SSIS Event Handler at Package Level

I have a fairly simple SSIS package (handful of Execute Task and a few Data Flow task). The end result I am looking to have: I would like to get notified by email when an error occurs anywhere in the package I would like that email to include:…
user507
2
votes
1 answer

SSIS Derived Column Casting to DT_STR

Why can't I get this to cast as a DT_STR - the data source is WSTR, but shouldn't this cast make the output DT_STR? (IPUNSPSC) =="**no_UNSPSC_available**" ? (DT_STR,20,1252)NULL(DT_STR,20,1252) : (DT_STR,20,1252)TRIM(IPUNSPSC)
Steve Britton
  • 67
  • 1
  • 1
  • 6
1
vote
1 answer

SSIS: Get Date for Day of the Week

In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date? I assume I have to do some subtraction on the day-of-week, but not sure: DATEADD("d", -DATEPART("DW", GETDATE()), GETDATE())
PeterX
  • 177
  • 1
  • 3
  • 10
1
vote
1 answer

How to create Destination table name with Getdate in SSIS OLEDB destination

How to create Destination table name with Getdate in ssis destination task. I have one table like Test..but i want to create new table like Test_09Feb2023 in OLEDB destination.
karthik sanapala
  • 37
  • 2
1
vote
0 answers

flat file datetime issue

I have a flat file with this datetime format 5/24/2017 05:12:44.243 PM. I have been struggling to convert this format to something like this 2017-05-24 17:12:44.243. I tried using this derived column expression …
gdw
  • 69
  • 1
  • 1
  • 4
0
votes
1 answer

SSIS Variables as expressions to generate Start and end dates of a quarter with input year and month passed as variable

Can someone help me create SSIS expressions for variables to generate QuarterStart(DateTime) and QuarterEnd(DateTime) as a variable with year(int32) and quarter(int32) passed on as user variables. The equivalent T-SQL script would be …
mk SQL
  • 5
  • 2
0
votes
0 answers

SSIS Dynamically Loop through SQL Tables

I have been fighting with an SSIS package for sometime now. I did successfully build a package that will extract data from a specific table by creating a date list from a script task, use a forEachLoop container to loop through the date list…
Stockburn
  • 501
  • 4
  • 22
0
votes
1 answer

Looking for solution to add double quotes in field which has commas in SSIS

I have a field called something like TYPE & the values are variant in it. I want the SSIS to export the result to CSV & while exporting, if it finds the , in column TYPE only that line/value should be double quoted. Eg: TYPE Begin Bag 1,000…
Atulya
  • 1
  • 1
  • 1
0
votes
1 answer

Logging the conditions within a conditional split transformation (SSIS in SQL Server 2012 and 2016)

I'm struggling to find out how we can store the conditions inside a conditional split transformation when we run a package. I need a snapshot of the expressions (the whole expressions, not only parameters inside them). The closest we have got is…
NikolaiH
  • 1
0
votes
1 answer

SSIS - Disable Task Based on Variable Value

I have an SSIS package that runs a number of Data Flow Tasks all at the same time. Each Data Flow Task is placed inside a Sequence Container. Before the Data Flow Tasks are run, a Script Task is used to check what Data Flow Tasks are required to…
Kevin
  • 533
  • 2
  • 12
  • 20
-1
votes
1 answer

How to format SSIS Expression for weekend

I have the following format to account for weekends while naming an excel output. How do I get only the date and not the full startime formatted like 20150429? ((DT_I4)DATEPART("weekday",@[System::StartTime]) ==2 ? Replace((DT_STR, 20,…
gaktheknife
  • 123
  • 2
  • 4
  • 16
1
2