Integration Services
8.7K views | +0 today
Follow
Integration Services
My collected tips on SSIS
Curated by Irawan Soetomo
Your new post is loading...
Your new post is loading...
Scooped by Irawan Soetomo
Scoop.it!

How to use variables in a Script Component

How to use variables in a Script Component | Integration Services | Scoop.it

There are two ways to read and write variables in a Script component. I will show you both. There is one big BUT for setting variables in a Script Component. You can't get the new variable value untill the Data Flow Task is finished. So you can't get its value within the same Data Flow Task.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

5 Tips for Developing SQL Server Integration Services Packages

5 Tips for Developing SQL Server Integration Services Packages | Integration Services | Scoop.it

Tips: Control Design Time Validation; Create a Package Template; Implement Package Configurations; Remove Leading and Trailing Spaces; Run Packages with the 32-Bit Runtime;

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Importing XML documents

Importing XML documents | Integration Services | Scoop.it

In this tip we look at how to import XML documents into SQL Server using SSIS. We will walk through an example and also show some of the potential issues and how to resolve them.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Enhancing an Error Output with the Script Component

By default, the two extra columns in an Integration Services error output, ErrorCode and ErrorColumn, contain only numeric codes that represent an error number and the ID of the column in which the error occurred. These numeric values may be of limited use without the corresponding error description and column name.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Running an SSIS Package as a SQL Server Agent Job doesn't copy files?


You need to create a Credential, a SQL Agent Proxy, and then assign the proxy account to the SQL Agent job step. Proxy accounts are specific to each subsystem (e.g Powershell, CmdExec, SSIS, etc.)

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Programmatically executing packages in the SSIS 2012 Catalog

After about a year of intending to get to grips with the pre-release versions of SQL Server 2012 (Denali) and failing to do so, I was fortunate enough to land a contract where I had the option to use it from my first day across most of the stack (SSAS, SSRS, SSIS, and the database engine).

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Implementing Foreach Looping Logic

Implementing Foreach Looping Logic | Integration Services | Scoop.it

With SSIS, it is possible to implement looping logic into SSIS's control flow in order to define a repeating workflow in a package for each member of a collection of objects. Rob Sheldon explains how to use this valuable feature of SSIS

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SSIS to SQL Server Data Type Translations

I have found often when teaching SSIS to others that it can be extremely confusing when you first encounter SSIS data types. At first glance they seem to be nothing like SQL Server data types you love and know. That's why I've provided below a conversion chart of SSIS data types to SQL Server data types. This information is readily available on MSDN but it always seems difficult to find. Hope this helps!

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

How to deploy and execute an SSIS package from the SSISDB catalog

How to deploy and execute an SSIS package from the SSISDB catalog | Integration Services | Scoop.it
In this walk-through we will create a simple SSIS package that writes to a database table, deploy the SSIS project containing the package to the SQL Server Integration Service's catalog and execute the package (1) from the SSIS Catalog's Execute Package GUI and (2) from a T-SQL script generated by the Execute Package GUI.
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

How to "downgrade" SQL Server Integration Services 2014 packages to 2012

How to "downgrade" SQL Server Integration Services 2014 packages to 2012 | Integration Services | Scoop.it

I  was able downgrade SSIS 2014 to SSIS 2012 packages by modifying the XML file manually. I downgraded 15 packages in 4 hours including finding the workaround described above. It should be faster if you already know what to do. This approach seems more reasonable than re-creating all the packages I designed and developed for weeks with all their DFTs and expressions. No, thank you. Let me know if you encounter any other exceptions or if any of the steps above is not clear.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Installing SQL Integration Services after SQL Cluster Setup has Completed

Installing SQL Integration Services after SQL Cluster Setup has Completed | Integration Services | Scoop.it

By just choosing “Integration Services” you can successfully install SSIS on each node of the cluster. You must run this setup on all nodes of the cluster where you want the SSIS service installed.

 

Alter your <ServerName>.\SQL2008</ServerName> property in the MsDtsSrvr.xml file to correctly point to your SQL virtual server name rather than the default “.\InstanceName” that is currently there.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Generate a GUID/newid()

Generate a GUID/newid() | Integration Services | Scoop.it

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
        Row.SQLNinjaGUID = System.Guid.NewGuid();
}

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Managing SSIS Security with Database Roles

Managing SSIS Security with Database Roles | Integration Services | Scoop.it

SQL Server 2012 introduced the new project deployment model for Integration Services. On the server side, this manifests in the SSIS catalog, the central point for managing SSIS projects, environments and parameters. But how can we assign permissions for various tasks to different users inside the catalog? How do we protect execution info of sensitive packages? This tip will guide you through the process of setting up security for the SSIS 2012 catalog.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Passing A DateTime Parameter Into The Execute SQL Task

Passing A DateTime Parameter Into The Execute SQL Task | Integration Services | Scoop.it

DATE doesn’t seem like the most logical type to choose in this scenario, which is why most people won’t even consider it. But it works! Actually, “DATE” is not really the best name that could be given to this particular data type.

Irawan Soetomo's insight:
more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

How to export data from database tables to an XML file

How to export data from database tables to an XML file | Integration Services | Scoop.it

A simple way to create an XML file from SQL Server tables using SSIS

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Integrating Lotus Notes to SQL using SSIS

Integrating Lotus Notes to SQL using SSIS | Integration Services | Scoop.it

I am going to discuss how to connect SQL Server to the Lotus Domino server. The steps involved in connecting to the Lotus notes mail server database can be used for connecting to other databases that reside in “Lotus Notes Domino” server.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Preparing a data migration plan

A practical introduction to data migration strategy and planning.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

How to Connect to Informix from an SSIS Package

How to Connect to Informix from an SSIS Package | Integration Services | Scoop.it

As with many 3rd party products IBM’s Informix OLEDB connection object is less then straight forward when it comes to configuring it to work with SSIS. After some digging I came up with the following way to connect to Informix database through SSIS in a Windows x64 environment. I had to go through stacks of IBM documentation, a lot of articles on the internet, and some trial and error before I reached a working solution. Eventually I was able to achieve the successful connection to an Informix database from an SSIS package (Works for Excel etc. too). Hopefully this post will save you some leg work.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

SSIS jumping in Visual Studio 2013 / SQL Server 2014

One really annoying bug in SSIS when using Visual Studio 2013 / SQL Server 2014 is the object jumping, so when you drag an object like a sequence container down the object shoots to the bottom. Thankfully the fix is simple. Close Visual Studio and apply SQL Server 2014 SP1 to your machine.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Where is the Business Intelligence Development Studio (BIDS) in SQL Server 2014?

Where is the Business Intelligence Development Studio (BIDS) in SQL Server 2014? | Integration Services | Scoop.it

Microsoft no longer provides BIDS with SQL Server 2014. Instead you need to download the SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT-BI) to get the functionality that was formerly in BIDS. SSDT-BI can be found at the Microsoft Download Center. The download is called SSDTBI_ x86_ENU.exe for the English language version. Running it launches a pared down version of the SQL Server setup program that you can see in Figure 1. This version of the setup program only offers the SQL Server Data Tools – Business Intelligence for Visual Studio and the SQL Client Connectivity SDK features.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Data Cleaning with Data Quality Services

Data Cleaning with Data Quality Services | Integration Services | Scoop.it

For data to be usefully analyzed, it must be consistent, accurate, and trustworthy. When incoming data is non-uniform, duplicated records are created and the data starts losing its value. In order counteract this issue, SQL Server's Data Quality Services (DQS) helps monitor and maintain incoming data, and deduplicates existing data using rules-based matching. Feodor Georgiev provides a thorough walkthrough on setting up DQS and creating the rules it uses to function as a first step towards data cleansing.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Foreach FTP file enumerator

Foreach FTP file enumerator | Integration Services | Scoop.it

There are a couple of options. You could use a Script Task, a wildcard in the RemotePath Property of the FTP Task or a Third Party component to download all files to a certain folder and loop through those files with a Foreach Loop file enumerator. This is the most common approach. 

Irawan Soetomo's insight:

http://www.sqlideas.com/2015/05/download-multiple-files-through-ftp-in.html

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Performing a Cross Join (Cartesian Product)

Performing a Cross Join (Cartesian Product) | Integration Services | Scoop.it

There are a few times where you might want to perform a cross join/cartesian join/cartesian product operation in SSIS.  One of those scenarios could be performing some kind of a double-sided "range join" where you can't line up a specific key in your data flow to a specific key in your lookup table.  Quite often you're planning to  have a Conditional Split right after your cross join operation to heuristically eliminate the rows that don't qualify for your "range" matching.

more...
No comment yet.
Scooped by Irawan Soetomo
Scoop.it!

Returning a Single-Row Result Set

Returning a Single-Row Result Set | Integration Services | Scoop.it

In this article, I demonstrated how to use an Execute SQL task to return a single-row result set with a single value and save that value to a variable. I then showed you how to use the variable in a second Execute SQL task to insert data into a table.

more...
No comment yet.