License: MIT License

TSQL 20.95% PowerShell 75.86% Shell 1.58% SQLPL 1.60%

azuresynapsescriptsandaccelerators's Introduction

Projects

This repository contains scripts that help you to accelerate your journey to implement workloads with Microsoft Azure Synapse Analytics, particularly to aid you to perform below tasks:

Download the contents of this repository and unzip.

Video Channel with Step by Step Assessment & Migration Steps

Assessment

Assess your current analytics workload hosted in on-premises or in-cloud data warehouses.

Supported Platforms:

APS (PDW)
DB2
Netezza
Oracle / Exadata
Snowflake
Teradata
SQL Server

Platforms in Development:

Google Big Query
Redshift

For information on how to assess, navigate to the Assessment folder.

Migration

Migrate data warehouses hosted in below platforms into Azure Synapse Analytics:

On-Premises or in-Cloud SQL Server Databases
On-Premises Microsoft APS (Analytics Platform System), formerly known as PDW

For information on how to migrate (Currently SQL Server and APS), navigate to the Migration folder.

Contributing

If you have feedbacks or ideas to improve the repository, please email to us at [email protected].

This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.

When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.

This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.

Trademarks

This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.

azuresynapsescriptsandaccelerators's People

Contributors

Stargazers

Watchers

azuresynapsescriptsandaccelerators's Issues

Got MethodInvocationException when using mssql to parquet

Windows version: windows server 2022
Power shell version: 7.3.9
SQL Server version: SQL Server 2019 Standard

I'm trying to export parquet from MSSQL server using Export-ToParquet.ps1 with Parquet.Net dependency.

I got theses message when I export decimal type from MSSQL
MethodInvocationException: Exception calling ".ctor" with "3" argument(s): "scale cannot be less than 1 (Parameter 'scale')

but the job still complete after this exception.

Here's my table schema and config file
schema:

CREATE TABLE [dbo].[DM_BedsClothes_2017](

        [Site] [varchar](4) NOT NULL,

        [Emplid] [nvarchar](10) NOT NULL,

        [BedClothes] [char](1) NOT NULL,

        [ReceiveDate] [varchar](10) NOT NULL,

        [Prices] [decimal](18, 0) NOT NULL,

        [CreateBy] [varchar](10) NOT NULL,

        [CreateOn] [smalldatetime] NOT NULL,

        [ModifyBy] [varchar](10) NOT NULL,

        [ModifyOn] [smalldatetime] NULL

) ON [PRIMARY]

config file:

Enabled,Database,JobName,Query,FilePath
1,"quickstart_db","select_test_job","select * from DM_BedsClothes_2017",".\Output\test2.parquet"

Should I convert the data type or other solution to solve this issue?

Config file with .sql file path as JobName

It would be nice to have the ability to address a .sql query file as JobName in config file.

Migrating from Azure SQL to Synapse Issue with Table create statements

I was following the instructions for running translatetableddls, table statements get created but they are missing the column information. gettting error with invoke sql-cmd.

Conversion

I am looking for a conversion from bit to smallint instaed of boolean and nvarchar to varchar

Special Characters in Table and Column names

Trying to use this utility and works great on table and column that have a standard naming convention. The tables and column I am working with contain a '$'.

I have tried to ESC in front of the $.

put [] around the table name
use %36 instead of the $

No error is generated. The shell of the file is created. No data.

Any suggestions?

Support for same table names in multiple schemas creates column list incorrectly

First of all, thanks so much for putting this project together. I am using it for a critical migration from On Prem SQL into Synapse dedicated SQL pool. One issue I have found is having the same table name under more than one schema results in duplicate column names when generating the CREATE TABLE statements. The issue is in file GetTableMetaData.sql under the folder "1_TranslateTableDDLs" as it does not take the schema into account in the Column_List CTE. Also need to account for passing in the schema name into this script. I will attach a screenshot of what I had to run manually to get around this but ideally I wanted to bring this use case to your attention as it would be nice to include into the tool

microsoft / azuresynapsescriptsandaccelerators Goto Github PK