gm23
Novice

Execute SQL Code in SmartObject

Hello, 

 

I am trying to create a smart object to bulk upload a csv file to a database to allow users to upload and download multiple data points at once. Does anyone have any experience doing this? Do you know what method could be used?

 

The sql code would hopefully follow 

BULK INSERT [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ] FROM 'data_file' [ WITH ( [ [ , ] BATCHSIZE = batch_size ] [ [ , ] CHECK_CONSTRAINTS ] [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ] [ [ , ] DATASOURCE = 'data_source_name' ] [ [ , ] ERRORFILE = 'file_name' ] [ [ , ] ERRORFILE_DATASOURCE = 'data_source_name' ] [ [ , ] FIRSTROW = first_row ] [ [ , ] FIRE_TRIGGERS ] [ [ , ] FORMATFILE_DATASOURCE = 'data_source_name' ] [ [ , ] KEEPIDENTITY ] [ [ , ] KEEPNULLS ] [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ] [ [ , ] LASTROW = last_row ] [ [ , ] MAXERRORS = max_errors ] [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] [ [ , ] ROWS_PER_BATCH = rows_per_batch ] [ [ , ] ROWTERMINATOR = 'row_terminator' ] [ [ , ] TABLOCK ] -- input file format options [ [ , ] FORMAT = 'CSV' ] [ [ , ] FIELDQUOTE = 'quote_characters'] [ [ , ] FORMATFILE = 'format_file_path' ] [ [ , ] FIELDTERMINATOR = 'field_terminator' ] [ [ , ] ROWTERMINATOR = 'row_terminator' ] )]

 

However things like batch size would vary by upload. 

 

Any suggestions on this or other ways that users may bulk upload would be highly appreciated! Thanks, 

 

 

0 Kudos
Reply
1 Reply
Vernon
Contributor

Re: Execute SQL Code in SmartObject

Interesting scenario.


One option would be to wrap this in a Stored procedure and then make use of the SQL Service to execute it. The only other option I can think of is to try to make use of the market PowerShell broker.


http://community.k2.com/t5/K2-blackpearl/PowerShell-Wizard/ba-p/981


http://community.k2.com/t5/K2-blackpearl/PowerShell-Service-Object/ba-p/1025


 


hope this helps


Vernon

0 Kudos
Reply