Discover this year's submissions to the Dataiku Frontrunner Awards and give kudos to your favorite use cases and success stories!READ MORE

reload data from SQL-connection

Solved!
hop
Level 2
reload data from SQL-connection

Hi,

I have connected to an MS SQL Server and I am extracting data on a daily basis in one of my projects. How can the data I'm extracting be reloaded automatichly - lets say every morning?

Henrik


Operating system used: Windows 10

0 Kudos
1 Solution
dgraham
Dataiker
Dataiker

Hi @hop,

The error occurs because the connection of the dataset does not have permission to create tables in the SQL database. The simplest solution is to grant the user that is specified in the connection create table permission in the database.

In order to update or write data into a SQL datasets, DSS first drops the table  > it then creates the table > and finally inserts the data. You could change this behavior such that table creation occurs only if the table doesn't already exist in the database by enabling the "Truncate to clear data" option in the setting of the connection. With this option enabled, DSS creates the table if it doesn't already exist, table creation is skipped if the table already exists > it then checks whether the table schema matches the current schema of the dataset in DSS > if it matches, it truncates the table > then inserts the data. Note, if the table and dataset schemas are different, then the write operation will error.

View solution in original post

0 Kudos
2 Replies
hop
Level 2
Author

Okay so I found out that the data will be reloaded when I rebuild the flow. But when I try to rebuild I get the error 

'CREATE TABLE permission denied in database'

How do I solve this?

0 Kudos
dgraham
Dataiker
Dataiker

Hi @hop,

The error occurs because the connection of the dataset does not have permission to create tables in the SQL database. The simplest solution is to grant the user that is specified in the connection create table permission in the database.

In order to update or write data into a SQL datasets, DSS first drops the table  > it then creates the table > and finally inserts the data. You could change this behavior such that table creation occurs only if the table doesn't already exist in the database by enabling the "Truncate to clear data" option in the setting of the connection. With this option enabled, DSS creates the table if it doesn't already exist, table creation is skipped if the table already exists > it then checks whether the table schema matches the current schema of the dataset in DSS > if it matches, it truncates the table > then inserts the data. Note, if the table and dataset schemas are different, then the write operation will error.

0 Kudos