Microsoft SQL - Integration Services SSIS

SSIS project foreach loop editor does not show configuration for ADO or ADO.NET enumerator

UPDATE on 2017-05-31:It seems to be a bug with version 17.1 (build 14.0.61705.170). I uninstalled that version (including some prerequisite and other components from program list), did a repair on Visual Studio 2015, then installed build 14.0.61021.0. I was able to see the ADO components working again (but lost the ability to deploy to SQL 2017, as expected). I will report this to the SSDT team.ORIGINAL POST:I set up Visual Studio 2017, but the SSDT for SQL 2016 did not integrate so I am in Visual ......

Posted On Friday, May 26, 2017 2:39 PM | Comments (3)

To bcp or Not To bcp... SSIS won (very specific Req)

I'm a recent convert to bcp. Actually, to put it more correctly, I haven't had any ETL requirement that would require complex data flow. I am a huge SSIS fan, but until 2012 came along, logging and debugging has always been an issue, so slowly I was moving back to SQL for basic tasks, breaking the uniformity of the project. Along the way I started to use bcp more heavily So imagine my surprise when I found this article: Basically saying "Data Flow Destination Fast ......

Posted On Tuesday, September 23, 2014 9:53 AM | Comments (0)

OLEDB going away? Microsoft claims to be deprecating it in favor of ODBC

In case you haven't heard. Microsoft claims to be deprecating OLEDB in favor of ODBC. Seems like they want to push as many people to the cloud as possible... http://social.technet.micro... ......

Posted On Thursday, June 19, 2014 3:55 PM | Comments (0)

Very honored to be referenced by MSSQLTIPS

I am very honored that one of my blogs is being referenced by MSSQLTips as exam material help. I started this blog a while ago because I got frustrated with forgetting some cool stuff I've done before, and I am very glad that it not only helped me, but help some in the community. Microsoft developer community has been so great, I'm happy to do my part. My blog being ......

Posted On Thursday, March 20, 2014 10:46 AM | Comments (0)

Very honored to be referenced by MSSQLTIPS

I am very honored that one of my blogs is being referenced by MSSQLTips as exam material help. I started this blog a while ago because I got frustrated with forgetting some cool stuff I've done before, and I am very glad that it not only helped me, but help some in the community. Microsoft developer community has been so great, I'm happy to do my part. My blog being ......

Posted On Thursday, March 20, 2014 10:28 AM | Comments (0)

Different Mind Set Between Data Warehouse and System Integration

I love doing database design with people. I've done my fair share of database modeling on my own; those are the easy projects, sure, but with no feedback. However, working with people on database never fails to help me look at things from different perspective.A very simple thing like whether a NOT NULL table column should have default values get very different reaction from a BI guy and a System Integration guy. Let me put some of the priorities out from different projects I've been in to help understand ......

Posted On Friday, March 1, 2013 2:54 PM | Comments (0)

SSIS Catalog: How to use environment in every type of package execution

Here is a good blog on how to create a SSIS Catalog and setting up environments. Here I will summarize 3 ways I know so far to execute a package while using variables set up in SSIS Catalog environment. First way, we have SSIS project having reference to environment, and having one of the project parameter using a value set up in the environment called "Development". ......

Posted On Thursday, November 22, 2012 6:59 AM | Comments (2)

SSIS 2012 formating quirks

There are so many funny quirks in SSIS 2012 that I have to list them, to save other people from the misery. If you want to move items to one direction, make sure you "grab" the opposite side. For example, you want a whole bunch of data flows to move up, select them all and grab the lowest item. When you drag the arrow to connect Precendence Constraint, make sure you drop it on the area of target that has no text, otherwise, it thinks you want to edit the text and change the target item layout ......

Posted On Wednesday, November 21, 2012 12:21 PM | Comments (0)

SSIS 2012 project.param Add Parameters to Configurations

Always was able to do it in a .NET application and a reporting project.  This took me a while to realize.  In SSIS 2012 project, once you create project.params, you can add them to your configuration settings by clicking this button

A dialogue pops up where you can add your project params and specify their values in different configuration set.

Posted On Wednesday, November 21, 2012 11:24 AM | Comments (0)

Using stored procedure to call multiple packages at the same time from SSIS Catalog (SSISDB.catalog.start_execution) resulted in deadlock

Refer to my previous post ( about dynamic package calling and multiple packages execution in these posts: I only saw this twice, other times the stored procedure was able to call the packages successfully. After the service pack, I haven't seen it...yet. ......

Posted On Wednesday, November 21, 2012 10:45 AM | Comments (0)

Deploying SSIS to Integration Services Catalog (SSISDB) via SQL Server Data Tools

There are quite a few good articles/blogs on this. For a straight forward deployment, read this ( For a more dynamic and comprehensive understanding about all the different settings, read part 1 ( and part 2 ( ......

Posted On Thursday, November 8, 2012 10:05 AM | Comments (0)

SSIS Tools

Somebody told me this and I'm ready to try some of the components:

FileWatcher, Regex Clean, RowNumber Transform

Posted On Thursday, November 3, 2011 3:03 PM | Comments (0)

Informative SSIS error message to be used in notification expression

"The " + @[System::TaskName] + " task captured error in the " +  @[System::PackageName] + " package at " + (DT_WSTR, 50) @[System::EventHandlerStartTime] + ". The message is:

" + @[System::ErrorDescription]

Posted On Wednesday, January 12, 2011 4:53 PM | Comments (0)

SSIS design consideration

The following is my experience with SSIS building: Make sure you understand the 3 modes of Lookup. General, I would only do full cache if the lookup table only return less than 200 rows Never use the table or view select unless you really need every field on that table or view. select * from a data source will break in the future if more columns are added to the table select * in a lookup brings back unnecessary fields and impact performance Never use "Sort" in SSIS unless it's absolutely necessary. ......

Posted On Tuesday, October 25, 2011 10:16 PM | Comments (0)

Monitoring BizTalk Server Without SCCM, using SQL query

For the environment that does not have SCCM to monitor BizTalk server health, the following SQL query can be incorporate into a SQL job and have BizTalk server at least on a "constant" check. Note: be aware that the following was tested only against a single suspended instance with a send port. Since BizTalk SQL server does not allow select against the ServiceClass table, I couldn't use a single table join. With the data I currently have, I am not 100% sure the Receive Port, Pipeline, Orchestration ......

Posted On Wednesday, September 14, 2011 10:37 PM | Comments (0)

Frustration and rewards on using SSIS Pivot and Unpivot

Granted, if you are just transforming data in the same SQL server, why would you do it in SSIS instead of just using Pivot and Unpivot in T-SQL query? But what about in the case where data comes from various sources? What I had fun doing was the ability to skip using Linked server, pull in data from different sources, like XML over web service. Another point that made me happy was the ability to simulate ROW_NUMBER() function with the merged data by using a Script Component. Anyway, I highly recommend ......

Posted On Friday, May 20, 2011 3:36 PM | Comments (0)

Parallel programming in Visual Studio

Not much experience with these products yet.


Not free:


For SSIS, this looks very interesting:



Posted On Thursday, March 31, 2011 12:26 PM | Comments (0)

Stop a SQL job in the first 5 days of the month, but report success

It is actually quite simple when I think this through: Make the first job step with this T-SQL query: IF ((SELECT DAY(GETDATE())) < 6) BEGIN SELECT 1/0 END Go to the Advanced Tab of the Job Step property page. Select "Quit the job reporting sucess" on the "On Failure Action" drop down. Note: You can use RAISERROR, but the Severity has to be set to greater than 10. To avoid future maintenance confusion, I decided to go with the basic fatal error generation ......

Posted On Tuesday, February 2, 2010 1:12 PM | Comments (0)

SSIS: copying tasks between packages


Always turn logging on as the last step just before you are ready to do test runs.  The reason being if you need to copy any task between 2 packages and the source package has logging turned on, it will throw an error.

Posted On Monday, January 4, 2010 12:16 PM | Comments (0)

BizTalk transaction even cover an "AFTER INSERT" trigger

This surprised me.  I have a BizTalk SQL adapter calling a stored procedure to insert a row in some table.  The table has an "AFTER INSERT" trigger on it.

The original design had the trigger fire off an SSIS package.  But when the SSIS is called, the row status is still not committed.  I thought since I'm using an "AFTER INSERT" trigger, I was allowed to do that.  Need to read more on the transaction nature of triggers, I guess.

Posted On Wednesday, December 30, 2009 12:25 PM | Comments (0)

Using BizTalk to bridge SQL Job and Human Intervention (Requesting Permission)

I start off the process with either a BizTalk Scheduler (http://biztalkscheduledtas... or a manual file drop of the XML message. The manual file drop is to allow the SQL Job to call a "File Copy" SSIS step to copy the trigger file for the next process and allows SQL Job to be linked back into BizTalk processing. The Process Trigger XML looks like the following. It is basically the configuration hub of the business process <ns0:MsgSchedulerTrigger... xmlns:ns0="urn:com:something ......

Posted On Friday, February 4, 2011 3:29 PM | Comments (0)

Using SSIS to send a HTML E-Mail Message with built-in table of Counts.

For the record, this can be just as easily done with a .NET class with a DLL call. The two major reasons for this ending up as a SSIS package are: There are a lot of SQL resources in the organization for maintenance, but not as many .NET developers. There is an existing automated process that links up SQL Jobs (more on that in the next post), and this is part of that process. To start, this is what the SSIS looks like: The first part of the control flow is just for the override scenario. In the Execute ......

Posted On Wednesday, February 2, 2011 3:47 PM | Comments (17)

SSIS Send Mail Task and ForceExecutionValue Error

I tried to use the "ForcedExecutionValue" on several Send Mail Tasks and log the execution into a ExecValueVariable so that at the end of the package I can log into a table to say whether the data check is successful or not (by determine whether an email was sent out) I set up a Boolean variable that is accessible at the package level, then set up my Send Mail Task as the screenshot below with Boolean as my ForcedExecutionValueType. When I run the package, I got the error described below. Just to ......

Posted On Friday, December 3, 2010 11:34 AM | Comments (0)

A very strange problem with SSIS data dump into a pipe delimited file

[Update: 2010-07-14] Don't know what difference does one day make, but I re-tried this today and the file on the network looks fine. I know I'm not crazy because I tried the same steps yesterday twice and got screenshots in the email to prove it. At this point, I'm willing to take anyone's theory. [Original post] I ran into this problem and have yet to solve it. We needed to create a data dump pipe delimited flat file out of a table of 4 million+ rows. The output was created just fine on my local ......

Posted On Wednesday, July 14, 2010 11:34 AM | Comments (0)

Using SSIS as data source for SSRS

Gotta try this:

Posted On Thursday, May 13, 2010 10:40 AM | Comments (0)

Use expression to build the file connection string instead of using script task to build a variable

I've always used the Expression property to dynamically build the connection strings. It has always worked without any problem, so I never gave it much thoughts. However, a package we converted from DTS was built using script task to build the connection string before writing to the file in a data flow task. The reason it was converted into a script task was because the DTS package was using an ActiveX script to generate the file path, and it was a quick and dirty 5-minutes-job to convert that into ......

Posted On Wednesday, February 3, 2010 10:34 AM | Comments (0)

Execute SQL Job synchronously for BizTalk via a Stored Procedure call

The async one was very easy to do. However, in the process automation task I was given, there was a need to execute SQL Job and waiting for the execution result. I couldn't find anything on the web to do what I wanted, so I make the following stored procedure. In addition, this stored procedure is quasi-synchronous. I start the job and then check the status. You can change the timing of the status check, but for the example below, it is "synchronous within 30 seconds." I wish there is a better way ......

Posted On Monday, November 1, 2010 3:18 PM | Comments (0)

Final note on creating PDF using SSRS and SSIS

Business Scenario: The client gets some kind of feeds that writes into a process table of their client processing system. The data is stored directly as the raw XML file inside a SQL data column. The XML could store multiple enrollment information for the same member. We need to use this info to determine whether a member is eligible, then insert into a staging table. The staging table is then used as a feed to drive a report that generates an image for each claim and sent off to the different provider. ......

Posted On Tuesday, December 8, 2009 2:19 PM | Comments (0)

Calling SSRS report using either T-SQL or SSIS

[Update: 2009-12-30] We got it working using SSIS. Read this post: [Original post] Would love to know if there is a way to kick off a SSRS report and pass some parameters into it via T-SQL or SSIS. Unfortunately, we can't have MS SQL Enterprise edition. Guess this is the true list of possible solutions: VB script PowerShell WMI script batch file T-SQL Sad thing is I know how to make this ......

Posted On Tuesday, November 24, 2009 2:53 PM | Comments (1)

Using xp_cmdshell to execute SSIS in a stored procedure

This is the code: DECLARE @SQLCommand AS VARCHAR(1000) SET @SQLCommand = '("dtexec location" /F "package location and name" ' + '/SET \package.Variables[User::your variable name].Value;' + CONVERT(VARCHAR(2), MONTH(@Date)) + '/' + CONVERT(VARCHAR(2), DAY(@Date)) + '/' + CONVERT(VARCHAR(4), YEAR(@Date)) + ')' EXEC xp_cmdshell @SQLCommand Some notes: I tried to pass in the date without formatting it, I get a "Option "1" is not valid" message. I had to convert the date into a string to include in the ......

Posted On Wednesday, September 23, 2009 12:41 PM | Comments (0)

Different syntax to access variables in SSIS

In a Script Task (in control flow), it is Dts.Variables("variable_name")

In a Script Component (in dataflow), it is Me.Variables.variable_name

Posted On Wednesday, September 23, 2009 10:41 AM | Comments (0)

To capture all errors in an SSIS package

Script task to catch all SSIS errors. I then used an email task to send out the error report. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Run... Public Class ScriptMain 'Capture Error Public Sub Main() Dim msgs As Collections.ArrayList Try msgs = CType(Dts.Variables("ErrorC... Collections.ArrayList) Catch ex As Exception msgs = New Collections.ArrayList End Try msgs.Add(Dts.Variables("Err... Dts.Variables("ErrorCollect... ......

Posted On Friday, September 11, 2009 10:50 AM | Comments (2)

How to add leading zero's on a numeric value to a fixed width flat file field

Code: Man, I looked at this code and was wondering what the heck I was doing. Then I remember this was done to convert a MONEY datatype to a number with 2 decimal places. If you are working with an integer, the code is simple: REPLICATE("0",(5 - LEN(TRIM((DT_WSTR,4)[field_... + TRIM((DT_WSTR,4)[field_name]) The code above returns a 5-character string with leading 0's. I believe the following code returns a 15-character long string, can't be 100% sure at this moment because I can't remember ......

Posted On Friday, September 11, 2009 10:23 AM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski