2

I would very much like to add today's date while distributing a column that already has the time values to DateTime together as Date. Is there a way that you can do this in SSIS? Any idea how to do this?

I would like (today's date added like this using SQL and/or SSIS):

 OLEDB Table (datetime datatype):

 Date
 2017-03-11 09:00:00.000
 2017-03-11 10:00:00.000
 2017-03-11 13:45:00.000

 CSV Flat File (nvarchar(50)):
 Already have:
 Time
 9:00
 9:00
 9:00
 9:00
 9:30
 9:30
10:00
10:00
10:00
[Code]
 --Maybe something like this? Not sure how to make this work.
 GETDATE() +''+[Time]
[/Code]

Any ideas would be appreciated greatly!

Hadi
  • 36,233
  • 13
  • 65
  • 124
Chris Singleton
  • 157
  • 1
  • 1
  • 12
  • It's really not clear what you want. Would you like to turn `09:00` into `2017-03-11 09:00`? What data types are the two columns (the source and destination) – Nick.Mc Mar 12 '17 at 06:48
  • Hi Nick, I want to turn csv time into datetime in a OLEDB table with today's date. I think the time column is just a string comma delimited. – Chris Singleton Mar 12 '17 at 06:53
  • You're loading this data into a database table in SQL Server I guess? I assume the target column in the table is of datatype `datetime`? Finally, I guess you are using a derived column transform? – Nick.Mc Mar 12 '17 at 07:19
  • As a clue: this expression generates a piece of data that can be inserted into your table: `(DT_DBTIMESTAMP) "2017-03-11 09:00:00.000"`. You need to work out the different pieces and stick them together – Nick.Mc Mar 12 '17 at 07:23

1 Answers1

1

After looking to the sample you provided in your question your datetime format is (HH:mm,H:mm), so you can achieve this using an SSIS package and writing a VB.net script into it as the following:

  1. Create a Flat File connection manager and an OLEDB Connection Manager (Source and Destination)
  2. Add a DataFlow Task
  3. In the DataFlow Task add a Flat File Source , Script Component , OLEDB Destination
  4. your DataFlow should look like Source --> Script --> Destination
  5. In the Script component Mark the Time column as input( assuming it's name is inTime) and add an Output Column (ex: OutTime) of Time DT_DBTIMESTAMP

  6. In the Script Write The Following Code: (using Vb.net)

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

    If Not Row.inTime_IsNull AndAlso _
       Not String.IsNullOrEmpty(Row.inTime.Trim) Then

        Row.OutTime = DateTime.ParseExact(Row.InTime.Trim,New String(){"HH:mm","H:mm"},New System.Globalization.CultureInfo("En-GB"), System.Globalization.DateTimeStyles.None)

    Else

        Row.OutTime_IsNull = True

    End If

End Sub

C# Version (used Telerik converter)

   public override void Input0_ProcessInputRow(Input0Buffer Row)
    {


        if (!Row.inTime_IsNull && !string.IsNullOrEmpty(Row.inTime.Trim()))
        {
            Row.outTime = DateTime.ParseExact(Row.inTime.Trim(), new string[] {
                "HH:mm",
                "H:mm"
            }, new System.Globalization.CultureInfo("En-GB"), System.Globalization.DateTimeStyles.None);


        }
        else
        {
            Row.outTime_IsNull = true;

        }


}

When Parsing a Time Value as a Date it will add by default Today Date to it.

  1. In the OLEDB Destination Map OutTime Column to the Destination Column
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hi Hadi, I'm currently using C# instead of VB, do you have a version with C# code? – Chris Singleton Mar 12 '17 at 19:21
  • @ChrisSingleton i am not a good c# developer but i used a converter to achieveit and it sounds good, have a look – Hadi Mar 12 '17 at 20:34
  • Awesome! Thank you so much for posting this! Another question, can this work when you have more than one column with the time in one and other data in other columns? Or is there a way you can use this only on one column when you have data in others? Sorry for so many questions. – Chris Singleton Mar 12 '17 at 20:42
  • @ChrisSingleton script components can manipulate many columns at the same time, this is an example to manipulate one column, you can mark many columns as input and add many columns as output. – Hadi Mar 12 '17 at 20:46
  • @ChrisSingleton yes it works, i am waiting for your reply if it is not working – Hadi Mar 12 '17 at 21:07
  • A script task is unnecessary for this, but certainly use whatever technique you are comfortable with – Nick.Mc Mar 12 '17 at 23:18
  • Hi @Hadi, unfortunately, this script does not work in C#. It expects a return value as one of three errors. – Chris Singleton Mar 13 '17 at 16:14
  • @ChrisSingleton i will fix it. Give me some time. My code was generated using an online converter. Vb.net code working perfectly – Hadi Mar 13 '17 at 16:58
  • @ChrisSingleton I fixed my code, it was some missing parenthesis and some spelling error. check it up – Hadi Mar 13 '17 at 18:36
  • Hi Hadi, still have two errors. Is there other code that goes with this? It say's: "No Suitable Method found to override" and also "The type of namespace name Input0Buffer could not be found, are you missing a directive? " – Chris Singleton Mar 13 '17 at 20:47
  • @ChrisSingleton i tested this code and it works fine. – Hadi Mar 13 '17 at 20:49
  • In the script component window go to Input and Output Tab, what is the name of Input buffer you are using. The default name is `Input0` so the buffer name is `Input0Buffer` – Hadi Mar 13 '17 at 20:50
  • Make sure your script task is a synchronous script – Hadi Mar 13 '17 at 20:51
  • I'm using a "Script Task" in C#, which may be different from VB? I used the big edit button and it goes to the C# script. Once it goes to the C# script, I place the code there and still gives two errors. BTW: I named the script task Input0, just like you said and no change. I am thinking maybe VB is different in the Script Task than C#? – Chris Singleton Mar 13 '17 at 21:16
  • You have to use a script component not a script task. (Inside dataflow task) – Hadi Mar 13 '17 at 21:18
  • Plz follow the step by step answer – Hadi Mar 13 '17 at 21:18
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137964/discussion-between-chris-singleton-and-hadi). – Chris Singleton Mar 13 '17 at 21:59
  • 1
    @ChrisSingleton you can also try DateTime.ParseExact(DateTime.Now.ToString("yyyy-MM-dd") + " " + Row.inTime, new string[] { "yyyy-MM-dd HH:mm", "yyyy-MM-dd H:mm" }, new System.Globalization.CultureInfo("En-GB"), System.Globalization.DateTimeStyles.None); – Hadi Mar 14 '17 at 07:22
  • 1
    Thank you so much Hadi, your awesome! This seemed to work! – Chris Singleton Mar 15 '17 at 21:27
  • @ChrisSingleton take a look here http://stackoverflow.com/questions/42802362/time-parsing-issue-using-datetime-parseexact i asked this question related to your issue – Hadi Mar 15 '17 at 21:53