SSIS features face-to-face

-
SQL Server Integration Services provides a wide variety of features that helps developers to build a robust Extract, Transform and Load process. After many years contributing to SSIS related tags on Stackoverflow.com, I can say that many developers have some misunderstanding about SSIS features (SSIS OLE DB Source, SSIS Expressions, SQL Server destination …) especially those which are very similar and have some common usability.

comparing apples side by side

Last year, I started writing SQL related articles for the SQL Shack blog. In this post, I would like to share with you a series I have published a few months ago called "SSIS features face-to-face" which is composed of 11 articles where I made a comparison between similar SSIS features.

SSIS OLE DB Source: SQL Command vs Table or View

In this article, we do a comparison between SSIS OLE DB Source data access modes: “Table or View” with Specific columns selected Vs. Using “SQL Command” to select specific columns.

SSIS Expression Tasks vs Evaluating variables as expressions

In this article, I gave first an introduction about SSIS expressions, then I described briefly the Expression Task and how to Evaluate a variable as expression. Then I made a comparison between these two features to illustrate the similarities and differences between them.

SSIS OLE DB Destination vs SQL Server Destination

In this article, I gave an overview of SSIS OLE DB Destination and SQL Server Destination and I tried to illustrate some of the differences between both destination components based on my personal experience, SSIS official documentation and some other experts experience in this domain.

Execute SQL Task in SSIS: SqlStatementSource Expression vs Variable Source Type

In this article, I gave an overview of Execute SQL Task in SSIS and I tried to illustrate some of the differences between writing an expression to evaluate SqlStatementSource property or writing this expression within a variable and change the Execute SQL Task Source Type to a variable.

Execute SQL Task in SSIS: Output Parameters vs Result Sets

In this article, I described the difference between using output parameters and result sets within Execute SQL Task in SSIS.

SSIS Derived Columns with Multiple Expressions vs Multiple Transformations

In this article, I gave first an overview of SSIS derived column transformation, then I ran an experiment to check if there is any difference between adding multiple expressions within one derived column transformation and adding a derived column transformation for each expression.

SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations

In this article, I gave first an overview of SSIS data types and data types conversion methods and then I illustrated the difference between changing the columns data types from the Source Advanced editor and using Data Conversion Transformation.

SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET

In this article, I gave first an overview of the OLE DB, ODBC, and ADO.NET SSIS connection managers. Then I tried to illustrate the difference between them when trying to connect to SQL Server.

SSIS Flat Files vs Raw Files

In this article, I gave an overview of using Flat Files and Raw Files in SSIS, then I illustrated some of the differences between using these two types.

SSIS Foreach Loop vs For Loop Container

In this article, I described briefly foreach loops and for loops. Then, I gave an overview of SSIS Foreach Loop Container and For Loop Container. Finally, I made a comparison between these two containers.

SSIS: Execute T-SQL Statement Task vs Execute SQL Task

In this article, we will give an overview of Execute T-SQL Statement Task, and we will make a small comparison with the Execute SQL Task which is more popular.

SSIS Script Component vs Derived Column

This article is not a part of the series, but it was published previously by Daniel Calbimonte on the same website (SQL Shack) and it is very related to the series since it compares Script Component and Derived Column transformations.



Comments

Post a Comment

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin
Read more