CSV file size from "Save Results As..." versus exported via SSIS Package / Export Wizard
When exporting query results from the grid using "Save Results As...", I'm consistently creating CSV files that are half the size of CSV files created by exporting via the Import/Export Wizard (and the resulting SSIS Package). Exact same query used for both, same results, same number of records etc. I believe this has to do with the file encoding used by each export process. I know this is not a precise science, but based on how Notepad interprets the resulting files, the encoding of the "Save Results As..." files is "UTF-8 with BOM", while the SSIS Package generates a "UTF-16 LE" file. I've tried a variety of approaches to try to get the Import / Export Wizard to mimic the "Save Results As" encoding without success. Changing the Code Page field on the "Choose a Destination" screen to "65001 (UTF-8)" results in the dreaded "DT_NTEXT not supported with ANSI files" error upon export due to nvarchar(max) data types in the source table. That can be resolved by checking the Unicode box on the "Choose a Destination" page, but checking Unicode also disables the Code Page dropdown. Even if you select "65001 (UTF-8)" in Code Page and then check Unicode, it still produces a UTF-16 encoded file. Two questions at this point: 1) How does the "Save Results As..." function in the query grid avoid the DT_NTEXT error and produce a UTF-8 encoded file? 2) If I tried to edit the package in SSIS Designer, would I have more control over the encoding and be able to mimic the "Save Results As..." file sizes? This would take a bit of effort (installing Visual Studio data tools, learning how to use it, etc) so if that's a dead end I'd rather not pursue it.
SSIS DevOps Tools new version tasks in preview
SSIS DevOps Tools new enhancements: SSIS Build task version 1.* (Preview) Remove the dependency on Visual Studio and SSIS designer. Build task can run on Microsoft-hosted agent or self-hosted agent with Windows OS and .NET framework 4.6.2 or higher. No need of installing out-of-box components. Support protection level EncryptionWithPassword and EncryptionAllWithPassword. SSIS Deploy task version 1.* (Preview) Support protection level EncryptionWithPassword and EncryptionAllWithPassword.You can now skip SSIS package validation (new setting in SSIS extension 3.9)
With the new version of the SSIS extensions (v3.9), you can set an option to skip the validation of SSIS packages during opening. I've collected the information in a blog post: https://workingondata.wordpress.com/2020/08/24/how-to-skip-ssis-package-validation-new-in-ssis-extension-v3-9/ Have fun and faster SSIS development for you! 😉 WolfgangUse SSIS to load data in chunks from SQLServer to Excel
This blog provides a solution to use SSIS to load data in chunks from SQLServer to Excel. The sample package has below steps: Read row count of the data to load from SQL Server and save the row count in variable Split the rows to n chunks in a For Loop Container, create an Excel sheet for each chunk and load data in each chunk to Excel sheet
