Data Developers

Problem: How to remove from DateTime variable hours, minutes, seconds and other parts in SSIS If I have DateTime like  21 jul 2019 8:30:05 , and I want have it as  21 jul 2019 00:00:00  and still as DateTime not string Solution: You can achieve that using a derived column: ( DT_DBTIMESTAMP )( DT_DBDATE )@[ User :: DateTimeVariable ] Casting to  DT_DBDATE  will remove the time part, then recasting to  DT_DBTIMESTAMP  will re-add a time part but with  12:00 AM  value =  00:00:00 Example: Original post:  https://stackoverflow.com/questions/57221410/how-to-remove-from-datetime-variable-hours-minutes-seconds-and-other-parts-in/57221736#57221736

Problem: I have a SSIS package where I am running a SQL execution task to get some records, Then I am running a foreach loop iterating over ADO object returned and setting few variables values. These variables are used for data flow task queries. I need to set expression for the variable. In rows I have value something similar to  "foo" + @[User::Bar] + "baz"  (I set it using this expression directly, it works. I want to set it from sql output) But when I get this value from SQL task to Variable expression, SSIS is escaping the quotes, I do not want to escape these for my task to work. Saved Value : "foo" + @[User::Bar] + "baz" Variable set : \"foo\" + @[User::Bar] + \"baz\" Can anyone help me with this. I want expression as I have saved, due to this forceful escaping, my query is not getting set properly. Solution: 1st approach - Removing escape character using SSIS expression (This approach will only ...

In order to get the first and last day of the previous week, we can use the following expressions within derived columns: First day of previous week DATEADD ( "wk" , DATEDIFF ( "wk" , 7 , GETDATE ()), 0 ) Last day of previous week DATEADD ( "wk" , DATEDIFF ( "wk" , 7 , GETDATE ()), 6 ) References DATEADD (SSIS Expression)

Problem: I'm setting up a package to import an excel spreadsheet into a SQL database. There is a column in the spreadsheet where I would like to pick out keywords and then put them in a new column. In SQL it would be like a basic case statement case when column_A like '%Norwich%' then 'Norwich' when column_A like '%Ipswich%' then 'Ipswich' when column_A like '%Cambridge%' then 'Cambridge' else 'NA' end as NewColumn I have tried the below but I'm guessing its not working properly because I have now wildcards [ Report Title ] == "Norwich" ? "Norwich" : [ Report Title ] == "Ipswich" ? "Ipswich" : [ Report Title ] == "Cambridge" ? "Cambridge" : "NA" Example: Report Title NewColumn Norwich is in Norfolk Norwich Cambridge is in Cambridgeshire Cambridge Suffolk...