I was working on a SQL Server Integration Services package recently to load some data to a fact table in a smallish data warehouse. The data is made up of sales information for a set of months. The staging table contains a year number and a month number on each row. What I wanted to do was to get a list of all the distinct year / month combinations, and then process each batch in a data flow. This is a perfect use of the ADO ForEach enumerator component.
The control flow of the package looks like this:
The Execute SQL task at the start ("Get Distinct Years and Periods") has the following SQL:
SELECT DISTINCT Year_Num, Period_Num FROM dbo.Monthly7B ORDER BY 1,2
The results of the SQL query were placed into an Object variable called User::YearsAndPeriods. The ForEach enumerator then iterated over the rows in the dataset, assigning the first column (year_num) to an Int32 variable called User::YearNum and the second column (period_num) to an Int32 variable called User::PeriodNum.
After I built the package and ran it for the first time, though, I didn't get anything processed. Nothing failed, but the ForEach enumerator didn't fire even once. I added a breakpoint to the start of the data flow component, and found that the YearNum variable was correctly populated (with 2008), but that the PeriodNum variable was set to NULL, as was the PeriodStartDateId. I poked around a little bit, and then found that I had a mismatch in data types.
The PeriodNum column in the staging table was a SMALLINT, but the User::PeriodNum variable in the SSIS package was an Int32 (basically a regular 4-byte integer). I changed the User::PeriodNum variable in SSIS to be an Int16 type (a 2-byte integer) and the package worked normally.
I would have expected either: an error about a data type mismatch, an overflow warning, or the data types would implicitly convert the 2-byte integer to a 4-byte integer. However, the behaviour is obviously to fail silently without any help — which isn't too useful.