topic Re: Collection to SQL Server Database in Product Forum

Collection to SQL Server Database

DipinDevP — Thu, 14 Nov 2024 12:53:10 GMT

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!

Re: Collection to SQL Server Database

Mohamad_Dakkouri — Thu, 14 Nov 2024 18:46:39 GMT

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 ?

Re: Collection to SQL Server Database

michaeloneil — Fri, 15 Nov 2024 10:35:33 GMT

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

Re: Collection to SQL Server Database

DipinDevP — Sun, 17 Nov 2024 05:40:58 GMT

Thanks @Mohamad_Dakkouri @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?

Re: Collection to SQL Server Database

Mohamad_Dakkouri — Sun, 17 Nov 2024 05:59:51 GMT

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.

Re: Collection to SQL Server Database

DipinDevP — Sun, 17 Nov 2024 06:24:56 GMT

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

Re: Collection to SQL Server Database

Mohamad_Dakkouri — Sun, 17 Nov 2024 06:29:21 GMT

@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 ?

Re: Collection to SQL Server Database

DipinDevP — Sun, 17 Nov 2024 06:33:50 GMT

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

Re: Collection to SQL Server Database

Mohamad_Dakkouri — Sun, 17 Nov 2024 08:28:05 GMT

@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

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

Re: Collection to SQL Server Database

DipinDevP — Sun, 17 Nov 2024 12:28:37 GMT

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