Show SSIS project parameters in Visual Studio 2012

In SSIS 2012 there are two scopes on which parameters can be defined:

– project

– package

 

Projects parameters

If you want to show / edit SSIS project parameters in Visual Studio 2012:

Open the solution explorer and under the project, there’s a folder [Poject.params]:

Clicking on the folder [Poject.params] will show the project parameters.

image

 

Package parameters

If you want to show the packages parameters, open the package and click on the Parameters tab:

image

SSIS Fix: Validation error. The metadata for input column “…..” does not match the metadata for the associated output column.

 

After a schema change in the database I got the error: The metadata for input column “…..” does not match the metadata for the associated output column, on a Union all data flow transformation. To fix this problem:

Right click the "Union All"  > Edit…

Change the "Union All Input 2" value for the specific column to <ignore>:

image

 

Click on OK

Save package

Right click again on the "Union All"  > Edit…

Change the "Union All Input 2" value for the specific column to the correct value:

Click on OK

Save package

The error should be fixed.

Fix: The type initializer for ‘Microsoft.DataTransformationServices.Project.SharedIcons’ threw an exception. (Microsoft Visual Studio 2012)

image

 

After some automatic Windows updates, I was unable to open my SSIS projects in Microsoft Visual Studio 2012.

The following error was shown: The type initializer for ‘Microsoft.DataTransformationServices.Project.SharedIcons’ threw an exception.

 

After a google search, I found the following solution at:

http://social.technet.microsoft.com/Forums/en-US/829e1fca-fbbf-43d8-bde5-202ad95e8643/the-type-initializer-for-microsoftdatatransformationservicesdesignsharedicons-threw-an

 

Solution

Download and install http://support.microsoft.com/kb/2861107

SQLServer2012_SP1_CU5_2861107_11_0_3373_x64.

(The hotfix is 780MB, say what?!)

 

image

Implement conditional (IF THEN ELSE) logic based on a SQL Server Store Procedure result in SSIS 2012

There are many ways you could implement conditional (IF THEN ELSE) logic based on a SQL Server Store Procedure result in SSIS, here is just one way:

 

Note

For demo purpose I will be using the master database here.

 

Stored Procedure

Create sproc on master database

if  object_id('dbo.ColumnExists') is not null
begin
drop procedure dbo.ColumnExists
end
go
-- Determines if the given column exists in the given table.
create procedure dbo.ColumnExists
@TableName varchar(128), -- Full table name, like dbo.MyTable
@ColumnName varchar(128)
as
begin
set nocount on
declare @ColumnExistsResult bit = 0
-- Check if table exists.
if exists (select top 1 1 from sys.objects where Object_ID = Object_ID(@TableName)) 
begin 
-- Check if column exists in table.
if exists (select top 1 1 from sys.columns where name = @ColumnName and Object_ID = Object_ID(@TableName)) 
begin 
set @ColumnExistsResult = 1
end
end
-- Return single row to SSIS.
select @ColumnExistsResult as ColumnExistsResult -- This name should be used as ResultSet name in 
end
go

Add connection

image

 

 

Add package variables

image

 

For demo purposes I entered a default value for TableName (“dbo.spt_monitor”) and ColumnName (“lastrun”).

 

Add Execute SQL Task

Change Connection to the name of connection created in the previous step.

Change ResultSet to “Singel row”

Change SQLSourceType to “Direct input”, so the task will use the t-sql query in the property “SQL Statement”

Change SQLStatement to “exec dbo.ColunExists ?, ?”

Note multiple parameters should be separated by “,”.

 

image

 

Add parameter mapping

De parameters are linked to the question marks in the SQL Statement.

The first “?” in exec dbo.ColumnExists will be linked to Parameter Name “0” on the Parameter Mapping page.

Note on Date Type (data type “DATE” is datetime in t-sql, data type VARIANT_BOOL” is t-sql data type bit).

 

image

 

Setting Result Set

On the Result Set page you  can map the column names from the stored procedure call to SSIS package variables.

 

image

 

Setting the values for the precedence constraints

If  you want a data flow to be executed, when the “Execute SQL Task” fails or the given column does not exists, you should set the precedence constraint as follow:

 

image

 

If you want a data flow to be executed, when the “Execute SQL Task” succeeds and the given column exists, you should set the precedence constraint as follow:

image

 

The end result should look like

image

When we execute the package and the column exists the debugger should be paused on the left data flow:

image

 

When the column does not exist the debugger should be paused at the right dataflow:

image

 

 

When the “execute sql task” fails the debugger will be paused at the left dataflow:

image

How to handle variable column count with the SSIS 2008 Flat File Source.

If you want to import flat files (e.g. *.csv) that contain rows with dynamic column count, the best way is to use this codeplex component: http://ssisdfs.codeplex.com/

 

If you don’t want to use that component, here is a script task that alters the import file, before the flat file source executes.

 

 

public void Main()
{
Dts.TaskResult = (int)ScriptResults.Success;
// Files should contain 46 columns
int maxColumnCount = 46;
// Determines if an event can be fired more than once for the duration of the current execution.
Boolean fireAgain = true;
Dts.Events.FireInformation(0, "Info: ", "Update file process started (update file so it contains the correct column count per row).", string.Empty, 0, ref fireAgain);
// Get file path.
string importFile = Dts.Variables[@"User::FileToImport"].Value.ToString();
// Check if file exists.
if (System.IO.File.Exists(importFile))
{
// Will contain the new file content with corrected column count.
var newFileContents = new System.Text.StringBuilder(string.Empty);
using (var streamReader = new System.IO.StreamReader(spiFile))
{
// Continue reading until end of file.
while (!streamReader.EndOfStream)
{
// Read a line.
string line = streamReader.ReadLine();
// Ignore empty lines (empty lines will not be added to the new file contents).
if (!string.IsNullOrEmpty(line))
{
// Split on ",", so we can count columns. Use string[] for performance reasons (later the array is used to join).
string[] lineColumns = line.Split(',');
// Check if current row contains less columns, then max column count.
bool lessColumns = (lineColumns.Length < maxColumnCount);
if (lessColumns)
{
// Add column until maxColumnCount.
Array.Resize(ref lineColumns, maxColumnCount);
}
// Store only max column count in new file contents (ignoring extra columns).
string newLine = string.Join(",", lineColumns, 0, maxColumnCount);
newFileContents.AppendLine(newLine);
}
}
}
// Save new file contents.
System.IO.File.WriteAllText(importFile, newFileContents.ToString());
}
else
{
Dts.Events.FireInformation(0, "Info: ", string.Format("File: {0} not found.", importFile), string.Empty, 0, ref fireAgain);
Dts.TaskResult = (int)ScriptResults.Failure;
}
Dts.Events.FireInformation(0, "Info: ", "Update file process completed.", string.Empty, 0, ref fireAgain);
}

SSIS 2012 not showing correct Available External Columns in Xml Source Task.

 

The “ORDER” type in the following XML document will not be shown in the available external columns dialog in SSIS 2012.

<?xml version=”1.0″ encoding=”utf-8″?>
<ORDER>
  <ID>1</ID>
  <ORDERLINE>
    <ID>1</ID>
      <PRODUCTNAME>Test1</PRODUCTNAME>
  </ORDERLINE>
  <ORDERLINE>
    <ID>2</ID>
      <PRODUCTNAME>Test2</PRODUCTNAME>
  </ORDERLINE>
</ORDER>

 

Result

image

 

If you want the ORDER type to show up, add a root element to the xml document:

 

<?xml version=”1.0″ encoding=”utf-8″?>
<ROOT>
    <ORDER>
      <ID>1</ID>
      <ORDERLINE>
        <ID>1</ID>
          <PRODUCTNAME>Test1</PRODUCTNAME>
      </ORDERLINE>
      <ORDERLINE>
        <ID>2</ID>
          <PRODUCTNAME>Test2</PRODUCTNAME>
      </ORDERLINE>
    </ORDER>
</ROOT>

 

Now you will see both ORDER and ORDERLINE show up:

image

 

To add a root node to an XML document in SSIS you can use the following script code:

public void Main()
{
try
{
string selectedXmlFile = Dts.Variables[@"User::SelectedXmlFile"].Value.ToString();
string AlteredXmlFile = (String.Format("{0}x", selectedXmlFile));
Dts.Variables[@"User::AlteredXmlFile"].Value = selectedXmlFile;
XmlDocument oldDoc = new XmlDocument();
oldDoc.Load(selectedXmlFile);
XmlDocument newDoc = new XmlDocument();
XmlElement root = newDoc.CreateElement("ROOT");
newDoc.InsertAfter(root, null);
XmlNode content = newDoc.ImportNode(oldDoc.ChildNodes[2], true);
root.AppendChild(content);
newDoc.Save(AlteredXmlFile);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Variables[@"User::LogMessage"].Value = ex.ToString();
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

Installing SSIS support in Microsoft Visual Studio 2012

As of 5-mrt-2013, Microsoft Visual Studio 2012 supports SSIS projects by installing

Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012

 

You can download Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012 at:

http://www.microsoft.com/en-us/download/details.aspx?id=36843

 

On installation I got the following error:

SQL Server Setup could not search for updates through the Windows Update Service.

 

image

 

Well I just ignored that en clicked Next.

 

Than I got the error:

 

image

 

Rule "Same architecture installation" failed.

 

The CPU architecture of installing feature(s) is different than the instance specified. To continue, add features to this instance with the same architecture.

 

image

 

This was caused by selecting "Add features to an existing instance of SQL Server 2012" instead of "Perform a new installation of SQL Server 2012", this is the right option.

 

image

 

As mentioned on the installation page:

http://msdn.microsoft.com/en-us/library/jj856966.aspx

On the Installation Type page, verify Perform a new installation of SQL Server 2012 is selected, and then click Next.

Setup will not install a new instance of SQL Server 2012 Server, but will install new SQL Server Features, including SQL Server Data Tools- Business Intelligence for Visual Studio 2012,

which you will select on the next Feature Selection page.

Solving the SSIS error: The PrimeOutput method on component "…" (107) returned error code 0x80131502.

I was getting the errors:

 

Error 1

SSIS Error Code DTS_E_THREADFAILED.

Thread "SourceThread0" has exited with error code 0xC0047038.

There may be error messages posted before this with more information on why the thread has exited.

 

Error 2

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.

The PrimeOutput method on component "SCR Read fout" (107) returned error code 0x80131502.

The component returned a failure code when the pipeline engine called PrimeOutput().

The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

There may be error messages posted before this with more information about the failure.

 

Cause

This was caused by a SSIS script component, that had a vb substring error in it.

 

Solution

Fixing the substring exception fixed my problem.

Soloving the error "The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered" in Microsoft SQL Server 2008 R2

When you don’t have Microsoft Office 2007 installed on a machine running Microsoft SQL Server 2008 R2 Integration Services, you might get the error "The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered"

 

Solution

To fix this problem:

Download and install the ACE provider from the following link, this supports office 2007 files both xls and xlsx file.
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

 

 

Error

TITLE: Microsoft Visual Studio
——————————

Error at Org_eenheden [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

Error at Load data into SA [Organisatie eenheden [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

 

——————————
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

——————————
BUTTONS:

OK
——————————

Screendump

image