Sql-server – Best way to automate the loading of data daily into google big query from SQL Server

You have to deal with the unicode replacement character by calling it what it is:

Row.Description = Row.Description.Replace(NCHAR(65533), "a")

EDIT:

I apologize as I first gave you a pseudo-SQL Server solution, but with some additional research learned that SSIS behaves a little differently. I'm linking my source that gives multiple solutions, and for an answer will include the first. It seems that you could add a derived column component that would allow you to add a new column (e.g. strDescription from your table using the Type Cast operator DT_STR which will convert the unicode to string. You should then be able to use this derived column in place of the original column in you CSV destination. source Hope that helps!

Yes, stay away from the sp_OA* OLE Automation procedures.

If you want / need to handle this purely within SQL Server then you can do so using SQLCLR, which replaces the sp_OA* OLE Automation procedures. And if you aren't using xp_cmdshell for anything else, then no need to enable it just for this (although to be fair, if using SQL Agent, then a CMD step would be able to execute BCP and/or 7zip without the need for enabling xp_cmdshell).

With SQLCLR you can:

• do the export from within T-SQL as well and not need to mess with BCP which is an external process (see FileStream).
• zip the file, as a separate step, since. NET has methods to gzip and gunzip (see GZipStream).

• encrypt with a password (see RijndaelManaged and AesManaged). While you did specify AES, the MSDN documentation for AesManaged states:

  The AES algorithm is essentially the Rijndael symmetric algorithm with a fixed block size and iteration count. This class functions the same way as the RijndaelManaged class but limits blocks to 128 bits and does not allow feedback modes.

  The difference is if you are using SQL Server 2005, 2008, or 2008 R2. Those versions are limited to using .NET 3.5, and the MSDN documentation for AesManaged (for that version) states that it has a Host Protection Attribute (HPA) which means that any assembly using that class has to be marked as PERMISSION_SET = UNSAFE, whereas RijndaelManaged does not have that issue and can be used in an EXTERNAL_ACCESS assembly. However, it seems that the documentation for AesManaged shows that, starting in .NET Framework version 4.0 (SQL Server 2012, 2014, and 2016 use .NET Framework 4.0 and newer--currently up to 4.6), the note about the HPA has been removed. Even if UNSAFE is acceptable, please note that the specific HPA is MayLeakOnAbort, which means "might leak memory if the operation is terminated".

In this setup, the password is part of the encryption, not the compression. Hence you either have a zip file containing an encrypted file, or you have an encrypted file containing the zip file. The former method gives you a file that can be unzipped by anyone but then only read by someone with the correct password. The latter method requires knowing the password to get the compressed file, and also allows for better compression since the compression will be done on a text file (with a greater likelihood of compressible patterns).

While I have not implemented the encryption algorithms yet, I have coded both the file export (of any random query) and GZip/GZunzip in SQLCLR and can state that they do run efficiently, or at least can be made to be efficient (and can also be made to be inefficient). By this I mean I have used them to extract queries producing 5 GB text files, and GZipping those 5 GB text files, and memory was never adversely affected since both operations are streamed. Please note that the .NET-provided GZipStream class does not support the Zip64 format, and so is limited to 4 GB of source data.

For anyone who is interested in pre-built SQLCLR functions to do the extract and compression (the ones I mentioned above), they are available in the SQL# library (which I am the author of). Please note that:

• while there is a Free version of SQL#, the two objects mentioned here, DB_BulkExport and File_GZip, are only available in the Full version.
• in order to overcome the 4 GB limitation of the GZipStream class, I had to incorporate a 3rd-party library that does support Zip64.
• soon enough there will be functions to handle file encryption and decryption via Rijndael (available for SQL Server 2005 - 2016 at least) and Aes (available for SQL Server 2012 - 2016 at least).