l'Ishwar incroyable Nataraj

Now there are a lot of reasons why SSIS needs to be leveraged for loading data into the Azure DW platform. Even though Polybase and Azure Data factory are the core criteria's, here are the templates for the SSIS script task that were leveraged to load data (full and incremental into Azure DW) for a specific customer rather than using the data flow task: SSIS Full Load script : SSIS Incremental Load script : There are a few reasons for this approach and one of them being that the existing package was using a similar structure and one not to be deviated from. The other being that some key logging aspects needed to be handled in a Legacy platform that could not be decommissioned at that time.

The following scripttask showcases both the FTP and FTPS based connections for uploading a file (it can be modified to perform more options). My earlier approach was to leverage the FTPWebRequest but the "AUTH SSL" command was taking a huge amount of time so decided to fall back on using WinSCP instead. Also ensure that the WinSCP.exe is added to the path environment variable and the WinSCP.dll is placed in the GAC. public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public void Main() { /* * Traditional FTP */ /*ConnectionManager mgr = Dts.Connections.Add("FTP"); try { mgr.Properties["ServerName"].SetValue(mgr, "ftp://"+Dts.Variables["strFTPHost"].Value.ToString()); mgr.Properties["ServerUserName"].SetValue(mgr, Dts.Variables["strFTPUserName"].Value.ToString()); ...

Just a quick code snippet on the usage of the ScriptObjectModel in SSIS script tasks... It takes one through all the required functions that can be exposed using the script object model:- using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.SqlServer.Dts.DtsClient; using Microsoft.SqlServer.Dts.Runtime; using Microsoft.SqlServer.Dts.Tasks.ScriptTask; using Microsoft.SqlServer.Dts.Tasks; namespace ScriptObjectNotifier { public class SOCall { ScriptObjectModel objModel; public SOCall(ScriptObjectModel valObjModel) { objModel = valObjModel; } /// <summary> /// gets the connection string from a conn Parameter /// </summary> /// <param name="connName">name of the connection</param> /// <returns>string</returns> public string GetConnectionString(string connName) { string connection...

Finally a technical article after a reallllly long time............ Tip 1: Ensure that when you fetch data from an SQL server data source always use the "(NOLOCK)" option in the sql statement example: select studentid,name from student (NOLOCK) This ensures that a deadlock does not take place (best option to use in a multi user environment :) ). Tip 2: The maximum packet size that can be transferred across a network is approximately 4 MB. Here we could improve the performance of our data fetch from the source and data migration to destination by modifying the connection property of your SSIS package. Right click on the the connection and click properties, you will find a property called packet size: Make sure this packet size is about 32767 to get the maximum benefit across a network. Tip 3: When you have multiple tasks running in parallel in a package(The maximum an SSIS package can actually subdivide these tasks or SSIS can spawn a maximum of 8 processes), so it would make ...