question

isteyakahmad-5434 avatar image
0 Votes"
isteyakahmad-5434 asked CarrinWu-MSFT commented

SSIS : Load data from datatable object to SQL server database table

Hi Team,

I am reading data from flat file and populating into datatable object (DataTable dt = new DataTable()) in script task.

I need to load the datatable value from script task to oledb destination table.

Please advise how I can achieve this.

Thanks,
Isteyak Ahmad

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered COZYROC Suspended commented

Why this counter way, why not loading directly from flat file into database table?

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My Flat file is having JSON data which is not properly separated and not is right format (JSon data with proper tag)

below is one sample row

{"apeId":"677ac54736764c5ca42beb9bebc","apeName":"DISCOVERY STATION","id":"e1a3af5af0654178aff5ffc01fd014a7","imageUrl":"http://172.16.9:8080/427/21e3698fd99ffb5c.jpg","lat":"25.038544","lng":"55.146149","monitorTask":"Traffic","resultTime":"2021-03-26 16:05:25","sceneImgUrl":"http://172.16:8080/427/21e3d99ffb5c.jpg","subType":"CAR","subjectId":"H66221","trackId":"3275041647"}


So I have written a code to get the values and store in datatable in script component.

Now from datatable to SQL server table i need to import the data.


0 Votes 0 ·
COZYROC avatar image COZYROC isteyakahmad-5434 ·

I don't see anything wrong with the JSON data from the sample you have provided.

0 Votes 0 ·
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered

Hi @isteyakahmad-5434,

Thanks for your posting.

Please refer to this blog that explores the process of JSON data import in the SQL Server table using T-SQL and SSIS.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

isteyakahmad-5434 avatar image
0 Votes"
isteyakahmad-5434 answered CarrinWu-MSFT commented

Hi CarrinWu,

I am able to process of JSON data import in the SQL Server table when I do the indentation of my Data in proper JSON format, However in Case I m getting JSON data which is not properly indented.

Hence I am not able to read the file as JSON file but I have to read as Text file and get the Values only using regular expression.

As of now I have got the desired data ( values ) in DataTable , Now from data table to SQL server I need to load the data.

Thanks,
Isteyak Ahmad

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @isteyakahmad-5434, based on my research, maybe this thread could help you.

0 Votes 0 ·