Collection to SQL Server Database

[DipinDevP]

Hi All,

I have a requirement to write a large set of collection data to an SQL server database table. Currently I am looping collection and execute insert query one by one. 

Is there any method to insert all the collection data as a single unit or bulk in Blue Prism?

Thanks in advance!

[Mohamad_747]

Hi @DipinDevP 

Actually with one of my customer i work with Sql server and what we do is the following:

We loop all the collection and we insert the request in a text data item [query].

How?

With a calculation stage. Something like [query]&"your new insert request" and we store it in query text data item.

Then we make the execution with the data item [query] that contains our Sql insert request (in one time)

Is it clear for you ?

[michaeloneil]

Hi @DipinDevP 

For SQL server there is a bulkl insert query you can run but it will insert from a file rather than from a collection. e.g. BULK INSERT Sales.Orders FROM '\\SystemX\DiskZ\Sales\data\orders.dat'

If its a collection you need to use then likely you need to go through this line by line and insert each one using a loop or alternatively you can do this in a code stage using a for each loop after although this would do essentially the same thing it might work a little faster.

Hope this helps

[DipinDevP]

Thanks @Mohamad_747 @michaeloneilfor your suggestions!

I am trying both the approaches.

I am facing another issue now related to Time Zone, while using looping approach.

I am using Get CSV Text As Collection action (File-Utility Management object) to get the CSV data as collection, but Date Time column values are changing to UTC time zone even I tried with datatype Text in collection.

Example: CSV value - 05-11-2024 14:15:00
                Output Collection value - 05-11-2024 08:45:00 

Any suggestions to get the DateTime value as exactly in the CSV file?

[Mohamad_747]

Hi @DipinDevP 

There is a lot of way to do it.

If you are looking for rapidity we coded a custom code that do this. But before using jt you need to ensure that there is no row without a date type in your column.

If you are confortable with this i can give you the code.

[DipinDevP]

@Mohamad_747 There will be values always in DateTime column, which is mandatory to have value in it.

[Mohamad_747]

@DipinDevP 

I mean value that are not a date time datatype for instance first row is ok '23/03/2022 09:12:10' and second row is (-----). The code will not work because of the (----)

You see what i mean ?

[DipinDevP]

Yes. I understand. All the rows wiil have values in DateTime format in our case.

[Mohamad_747 · Accepted solution]

@DipinDevP 

Well, 

Follow these steps.

Go to colection manipulation add a new action name it [Apply Function UTC To Local on column - CUSTOM], then drag and drop a code stage and name it UTC To Local CUSTOM and publish it like this : 

Open the code stage and provide these for input : 

Do the same for output 

In Code stage put this : 

--------------------------------------------------------

Try

    Dim local As Date

    Dim columnIndex As Integer = In_DataTable.Columns.IndexOf(Field_Name)

    If columnIndex >= 0 Then

        For i As Integer = 0 To In_DataTable.Rows.Count - 1

            Dim utcDate As Date = In_DataTable.Rows(i)(Field_Name)

            Dim utcDateTime As DateTime = DateTime.SpecifyKind(utcDate, DateTimeKind.Utc)

            If Not String.IsNullOrEmpty(Time_Zone_ID) Then

                Dim tz As TimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(Time_Zone_ID)

                local = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, tz)

            Else

                local = utcDateTime.ToLocalTime()

            End If

local = DateTime.SpecifyKind(local, DateTimeKind.UTC)

            In_DataTable.Rows(i)(Field_Name) = local

        Next

        Out_DataTable = In_DataTable

        Message = ""

        Success = True

    Else

        Success = False

        Message = "Column '" & Field_Name & "' does not exist in the input collection."

    End If

Catch e As Exception

    Success = False

    Message = e.Message

End Try

--------------------------------------------------------

Like this : 

Your action should look like this : 

Dont forget to put the inputs in start stage and the output in end stage.

Save and go to process now.

Lets make a test. 

I have these csv file see below : 

When i import it inside Blue Prism i have this : 

Notice Blue Prism make the conversion to UTC by default. 

Now i want to use my CUSTOM CODE, see below the parameters : 

Notice that i leave the timezone empty because its already set by Blue Prism but you can provide

some of these inputs for timezone id : 

"Eastern Standard Time" , "Central Standard Time" , "Mountain Standard Time" , "Pacific Standard Time" , "Alaskan Standard Time" , "Hawaiian Standard Time", Central Europe Standard Time", "India Standard Time" and there is more 🙂

Try to leave it blank at the begining and if its not working for you try to put your time zone id.

Your process should look like this : 

Now run and you will have the same wanted value see below : 

If i answered your request please put best answer

[DipinDevP]

@Mohamad_747 It worked for me!
Thank you very much!