The job allows you to retrieve data from a Microsfot SQL server database and upload the result into a Google Cloud Storage bucket as a CSV file. You can pass the query you wish and the job will extract accordingly.
JOB DESCRIPTION
1 - The job will open a connection to the Microsoft SQL Server database using the input variable. In case of error establishing the connection the job will fail and stop.
2 - The job will retrieve the data using the query provided as input and store the result in a CSV file. The result is saved in a Dynamic column so that you do not have to manually change the schema.
3 - The CSV generated in the previous step will be stored in the desired Google Cloud Storage bucket.
4 - The temporary file is deleted.
HOW TO USE THE JOB
The job is fully dynamic. So you can :
- Copy the job from the DATA_OCEAN project and paste it into your project
- Drag and drop the copied job into your Talend flow
- Provide the necessary parameters to make the job work.
The parameters that you have to provide are highlighted here below.
CONTEXT VARIABLE | DESCRIPTION |
|---|---|
| bucketFolder | Bucket folder where the file will be stored. If you want to save the file at the root of the bucket, use "" . |
| bucketName | The name of the bucket where the file will be upload |
| csvSeparator | The separator that divides columns of the CSV file (it should be a single character) |
| database | The name of the Oracle database |
| fileName | Name the CSV file uploaded in GCS |
| host | The host of the MS SQL Server database (it can be the IP address or the FQDN) |
| outputDirectory | Directory where the file will be stored on the remote engine |
| port | The port that will be used to communicate with the server |
| pwd | The user's password |
| query | The query that you want to use to retrieve |
| schema | The schema containing the table or view you want to access |
| tableName | The name of the table or view that you want to get |
| user | The Oracle user to be used |
- The default date format is yyyy-MM-dd HH:mm:ss . If you wish to change it (for instance adding milliseconds, having only the date part...) you should modify this value from by editing the schema of the tDBInput component.