-2

I have a column called FileName and I wanted extract the date from the column as a Date column using Derived Column in SSIS. The FileName Data type is nvarchar(260)

FileName

M:\Mapping\Workforce_Planning\ABC\ABClrmp.full.2016-01-07.csv

Desired Result

2016-01-07

I have been using SQL task but I prefer to use Derived Column.

Update [dbo].[GET]
SET [Date] = CONVERT(date, REPLACE(SUBSTRING([FileName], LEN([FileName])-CHARINDEX('\',REVERSE([FileName]),0)+2, LEN([FileName])-(LEN([FileName])- CHARINDEX('\',REVERSE([FileName]),0))),'.csv',''), 121)
Hadi
  • 36,233
  • 13
  • 65
  • 124
Djbril
  • 745
  • 6
  • 26
  • 48
  • 1
    So what's stopping you? – Tab Alleman Feb 24 '17 at 18:03
  • Stack Overflow is not a code writing service, it's expected that you attempt to code this yourself. I would suggest you do some research on your issue (maybe try the search box at the top of the page) and make an attempt at writing some code yourself. If/when you come across any issues with your code ask again and explain what you have tried, and why it did not work for you. See How to Ask for help with asking a great question. – Renats Stozkovs Feb 24 '17 at 18:14
  • You can just as easily build the conversion in SSIS - Use `Expression` field – Renats Stozkovs Feb 24 '17 at 18:20
  • @Djbril Again, you are supposed to show an effort in trying to solve a problem. A simple Google search returns dozens of high-quality tutorials and examples of how to use Expression Builder in SSIS (here is an example http://pragmaticworks.com/Training/Resources/Cheat-Sheets/SSIS-Expression-Cheat-Sheet). It is very similar to what you have in SQL; if you try something and it doesn't work - come here for help. Generally people won't even try to help you if you don't show any effort in solving it yourself. – Renats Stozkovs Feb 24 '17 at 21:32
  • @Djbril or [this](http://stackoverflow.com/questions/16711349/ssis-expression-convert-date-to-string) – Renats Stozkovs Feb 24 '17 at 21:39

1 Answers1

1

Just add a derived column with the following expression

LEFT(RIGHT([Filename],14),10)

Read more about Derived Column Transformation in this MSDN article

Test

i tested this expression on the following path M:\Mapping\Workforce_Planning\ABC\ABClrmp.full.2016-01-07.csv and it gives the following result 2016-01-07

Hadi
  • 36,233
  • 13
  • 65
  • 124