String or Binary data would be truncated when using MERGE in SSIS

Normally this error occurs when the column in target table doesn’t have the same column length of the source table. To locate the particular column, try the following

SET ANSI_WARNINGS OFF

[Insert your MERGE Statement here]

SET ANSI_WARNINGS ON

Technorati Tags: SSIS

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

The issue may be because of the column having length greater than 50. By default all columns from the flat file get assigned the length of 50 and by increasing it the issue can be fixed.

a) Right click on Flat File Source > Show Advanced Editor

b) Expand Flat file Source Output > Output Columns > Select the column (you can see that from the error message) Increase the length to 255

Technorati Tags: SSIS

Validation error. File System Task: Variable "variablename" is used as source or destination and is empty

The Variable "variablename" hasn't been assigned a default value. Assign a default value and the error will disappear.

Note: This is not an issue in SSIS 2008 but an issue from SSIS 2012, SSIS 2014 and above.

Package Configurations missing in SSIS 2012

Couldn't find the package configurations tab in SSIS 2012. This is because Package Configurations in SSIS 2012 is available only for the Package Deployment Model and not for Project Deployment Model.

Make sure to convert the project to Package Deployment Model by right-clicking the project and click on Convert to Package Deployment Model.

Project consistency check failed. The following inconsistencies were detected: Test BIML [PackageName].dtsx has a different ProtectionLevel than the project.

If you get this error when trying to create a package using BIML in SSIS 2012 using Project Deployment Model, make sure that the Project ProtectionLevel matches the Package ProtectionLevel.

SSIS Best Practices 3 - Handling and Logging Data Load Errors

Normally you don't want a ETL load to fail because of any data errors. For instance, if you have a file with 1000 rows with 10 rows having some issues, you don't want the load to fail. Instead the package must successfully load the 990 rows and the 10 error rows need to be redirected to a separate table to identify the issue and fix them. If it is a flat file then it is worth while to generate and include a Row Number which will help in identifying the row in the flat file.

The error table will have all columns with data type of varchar(max) across all columns with additional columns like ErrorCode, ErrorColumn and ErrorDescription.

Error rows are handled in the Data Flow through the use of the Error Row Configuration properties. These properties tell the Data Flow Components what to do when a row fails an operation, such as data conversion or truncation.

Redirect Row Event Handler denotes that when a row reaches an error for any column marked as redirected, the entire row is sent out the red error path output which normally connects to the Error table destination. If the red error path output is not used then the row will be ignored out of the Data flow.

public override void Input0_ProcessInputRow(Input0Buffer Row) 
{ 
Row.ErrDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode); 
}

SSIS Best Practices 2 - Single Package vs Multiple Packages

When developing an ETL package the first thing that comes to the mind is whether to build single package doing the end-to-end flow or multiple packages doing the same.

Being an Architect, I have been challenged with this question many times from my development team especially around single package model helps in maintaining less number of packages whereas multiple packages creates an overhead with the number of packages.

One Package = One Unit of Work

It is always best practice to have one package doing only one thing. The following benefits can be achieved doing so.

• Less time spent waiting on design-time validation. SQL Server Data Tools has a rich interface that provides, among other things, a near real-time evaluation of potential metadata problems in the SSDT designer. If, for example, a table that is accessed by the SSIS package is changed, the developer will be presented with a warning (or an error, if applicable) in SSDT indicating that metadata accessed by the package has changed. This constant metadata validation is beneficial in that it can help to identify potential problems before they are pushed out for testing. There's also a performance cost associated with this. The length of time required for validation increases as the size of the package increases, so naturally keeping the packages as small as practical will cut down on the amount of time you're drumming your fingers on your desk waiting for validation to complete.
• Easier testing and deployment. A single package that loads, say, 10 dimensions has a lot of moving parts. When developing each of the dimensions within the package, there is no easy way to test just one element of the package (apart from manually running it in the SSDT designer, which isn't a completely realistic test for a package that will eventually be deployed to the server). The only realistic test for such a package would be to test the entire package as a server-based execution, which may be overkill if you're only interested in one or two changed properties. Further, it's not uncommon for organizations with formal software testing and promotion procedures to require that the entire thing be retested, not just the new or changed elements. By breaking up operations into smaller units, testing and deployment are usually less of a burden because you are only operating on one component at a time.
• Distributed development. If you work in an environment where you are the only person developing SSIS packages, this is less of a concern. However, if your shop has multiple ETL developers, those do-everything packages are quite inconvenient. Although it's much easier in SQL Server 2012 than in previous releases to compare differences between versions of the same package file, it's still a mostly manual process. By segmenting the workload into multiple packages, it's much easier to farm out development tasks to multiple people without having to reconcile multiple versions of the same package.
• Reusability. It's not uncommon for the same logic to be used more than once in the same ETL execution, or for that same logic to be shared among multiple ETL processes. By encapsulating these logical units of work into their own packages, it's much easier to share that logic and avoid duplicate development.

Also we follow the following design patterns

• Each dimension has a separate package.
• Each fact table has a separate package.
• Staging operations (if used) each have their own package.
• Dropping or disabling indexes, Truncating tables can be grouped together and can typically reside in the same package.

SSIS Best Practices 1 - Naming Conventions

Always name the tasks and components using the following acronyms at the beginning of the task and component names. Following these naming conventions will facilitate on-going maintenance as well as making any global changes to the packages as package updates can be performed programmatically.

Task	Prefix
For Loop Container	FLC
Foreach Loop Container	FELC
Sequence Container	SEQC
ActiveX Script	AXS
Analysis Services Execute DDL	ASE
Analysis Services Processing	ASP
Bulk Insert	BLK
Data Flow	DFT
Data Mining Query	DMQ
Execute DTS 2000 Package	EDPT
Execute Package	EPT
Execute Process	EPR
Execute SQL	SQL
File System	FSYS
FTP	FTP
Message Queue	MSMQ
Script	SCR
Send Mail	SMT
Transfer Database	TDB
Transfer Error Messages	TEM
Transfer Jobs	TJT
Transfer Logins	TLT
Transfer Master Stored Procedures	TSP
Transfer SQL Server Objects	TSO
Web Service	WST
WMI Data Reader	WMID
WMI Event Watcher	WMID
XML	XML
Expression	EXPR

Component	Prefix
DataReader Source	DR_SRC
Excel Source	EX_SRC
Flat File Source	FF_SRC
OLE DB Source	OLE_SRC
Raw File Source	RF_SRC
XML Source	XML_SRC
Aggregate	AGG
Audit	AUD
Character Map	CHM
Conditional Split	CSPL
Copy Column	CPYC
Data Conversion	DCNV
Data Mining Query	DMQ
Derived Column	DER
Export Column	EXPC
Fuzzy Grouping	FZG
Fuzzy Lookup	FZL
Import Column	IMPC
Lookup Column	LKP
Merge	MRG
Merge Join	MRGJ
Multicast	MLT
OLE DB Command	CMD
Percentage Sampling	PSMP
Pivot	PVT
Row Count	CNT
Row Sampling	RSMP
Script Component	SCR
Slowly Changing Dimension	SCD
Sort	SRT
Term Extraction	TEX
Term Lookup	TEL
Union All	ALL
Unpivot	UPVT
Data Mining Model Training	DMMT_DST
Data Reader Destination	DR_DST
Dimension Processing	DP_DST
Excel Destination	EX_DST
Flat File Destination	FF_DST
OLE DB Destination	OLE_DST
Partition Processing	PP_DST
Raw File Destination	RF_DST
Recordset Destination	RS_DST
SQL Server Destination	SS_DST
SQL Server Mobile Destination	SSM_DST

Note: Thanks to Jamie Thomson for the naming conventions.

Unable to cast COM object of type 'System.__ComObject' to class type 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Problem

Got this error when trying to use ExecuteSQLTask dll programmatically in .NET solution.

Solution

Continue loop in SSIS even after an Error

Scenario

Got set of messages to process using a For Loop in SSIS. But when there is an error in one message then the rest of the messages are not processed. Given the child package failed the parent package is also failed. How to continue with the messages even a message has errors?

Solution

Set the MaximumErrorCount property of the For Loop container to 0. FailPackageOnFailure: When set to True, the failure of the task or container results in the failure of the package in which it is defined. FailParentOnFailure: When set to True, the failure of the task or container results in the failure of its container. MaximumErrorCount: Property specifies the maximum number of errors that can occur before the item fails.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'xxxxxx.dll, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7e7edcc6a6871fc7' or one of its dependencies. The system cannot find the file specified

• Check whether the DLL is deployed successfully in GAC (Global Assembly Cache)
• If not make sure that the DLLs are available in the following locations
• D:\Program Files\Microsoft SQL Server\100\DTS\Binn (depending on the version of SSIS)
• D:\Program Files (x86\Microsoft SQL Server\100\DTS\Binn (depending on the version of SSIS)
• Also make sure that the DLL is compiled for the right platform (32-bit or 64-bit or both)

SSIS FTP Step gives an error "The Password Was Not Allowed" and doesn't connect to the FTP Server

• Right click on your FTP connection manager, select properties and type the FTP Password.
• Save the package with EncryptSensitiveWithPassword property and provide a password and save the package and execute.

Milliseconds and SSIS DateTime Variables

Want to do delta loads for your Data Warehouse and having problems in storing Last Load time in an SSIS DateTime Variable?

SSIS DateTime data type truncates the milliseconds from the DateTime. To avoid this set the data type for the variable to store the Last Load time as String. e.g.

SELECT CONVERT(VARCHAR(23), LastModified, 121) LastModified FROM ........

Then you can change it to DateTime before passing it back to the database.

.......WHERE LastModified > CONVERT(DATETIME, @LastModified, 121)

"[OLE DB Destination [48]] Error: The input "OLE DB Destination Input" (61) cannot be set to redirect on error using a connection in a transaction

Problem

This issue raises if you try to use TransactionOption Required in SSIS while you have error rows to redirect between an OLE DB Source and an OLE DB Destination.

Solution

Instead try using a 3 Execute SQL tasks in the Control Flow with the first one before the data flow task with SQLStatement BEGIN TRANSACTION.

Put the rest of the 2 Execute SQL tasks in the Control Flow below the Data Flow Task with the precedence Constraint set for one as 'Failure' with the SQLStatement ROLLBACK TRANSACTION and the other one with the precedence Constraint set as 'Success' with the SQLStatement COMMIT TRANSACTION

Note: Also make sure to set the RetainSameConnection property on the appropriate ConnectionManager to True.

SFTP using SSIS 2008 & 2008 R2

SSIS doesn't have a built-in component to support SFTP but the following codeplex component can be used or create a script task and write the SFTP file retrieval.

http://ssissftp.codeplex.com/

Note:

• Copy the SSISSFTPTask100.dll to C:\Program Files(x86)\Microsoft SQL Server\100\DTS\Tasks\
• Register the SSISSFTPTask100.dll in the GAC using gacutil.exe (available under C:\Program Files(x86)\Microsoft SDKs\Windows\v7.0A\Bin)
• Open BIDS and click Tools > Choose Toolbox items > SSIS Control Flow Items > SFTP Task (Note: The SFTP Task will appear only if the SSISSFTPTask100.dll has been successfully registered in the GAC.

Single row result set is specified but no rows were returned

If you try to execute a SQL query in SSIS with the resultset as 'single row' and if your query doesn't return any records then you will get this error.

Solution

IF NOT EXISTS (Query Here)

  SELECT ''

ELSE

  Query Here  

SSIS SQL Task - “Parameter name is unrecognized”

Scenario

You tried to set up a SSIS SQL Task and also assigned a parameter to that.

Solution
Make sure that you changed the Parameter name from 'NewParameterName' to 0.

How to create XSD file path as expression for XML Source in SSIS

In your CONTROL FLOW tab, select the data flow step that contains the XML Source step. Do not go into the dataflow, it must be done in control flow.

1. Bring up its properties (right-click > Properties)
2. Click the elipsis button next to EXPRESSIONS
3. Select the [XML Source].[XMLSchemaDefinition] property then hit the elipsis button to the right of the Expression textbox to set the XSD File path
4. Drag and Drop your variable that holds your XSD path into the expression box, you should be able to evaluate expression here.
5. Click OK
6. Click OK again
7. Done

How to get files to the Miscellaneous folder in SSIS project?

Right click on the SSIS project and click on Add -> Existing Item and navigate to any file that you’d like to add to the project.

Files of a different type than the usual SSIS files such as .dtsx and .ds are automatically added under the Miscellaneous folder.

When adding files, they will be automatically copied to the SSIS project folder, no matter where they were stored originally.

Dynamic Sheet Name in SSIS Excel Spreadsheet imports

Scenario

I had to import excel data (just one sheet) into SQL database. The excel spreadsheet had multiple sheets where I am interested in only one sheet.

Solution

a) Create a new SSIS variable named "SheetName"

b) Create a script Task just before the Excel Source Task.

c) Add the following code to the main method of the script task

string excelFile = null;
string connectionString = null;
OleDbConnection excelConnection = null;
DataTable tablesInFile = null;
int tableCount = 0;
DataRow tableInFile = null;
string currentTable = null;
int tableIndex = 0;
string[] excelTables = null;

excelFile = Dts.Variables["User::ExcelFile"].Value.ToString();

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0";

excelConnection = new OleDbConnection(connectionString);

excelConnection.Open();
tablesInFile = excelConnection.GetSchema("Tables");

tableCount = tablesInFile.Rows.Count;
excelTables = new string[tableCount];

foreach (DataRow tableInFile_loopVariable in tablesInFile.Rows)
{
tableInFile = tableInFile_loopVariable;
currentTable = tableInFile["TABLE_NAME"].ToString();
excelTables[tableIndex] = currentTable;
tableIndex += 1;
}
}

Dts.Variables["User::SheetName"].Value = excelTables[0];

Dts.TaskResult = (int)ScriptResults.Success;


d) In your Excel source select the "Data access mode" as "Table name or view name variable".

e) Select the "User::SheetName" variable from the "Variable Name" dropdown list.