SSIS Error - The value cannot be converted because of a potential loss of data

Error

[OLE DB Source [167]] Error: There was an error with OLE DB Source.Outputs[OLE DB Source Output].Columns[MyColumn] on OLE DB Source.Outputs[OLE DB Source Output]. The column status returned was: "The value could not be converted because of a potential loss of data.".

Solution

Go into the source component then right clicking on it and choose "Show Advanced editor..."

From the Advance Editor select Input and Output Properties

Expand the OLE DB Source Output
Then expand the Output Columns
Select the column generating the error

O the properties section, scroll to the Length Data Type Property and increase the value to  something like 255.

SSIS Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

If you get the error:

Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

If the problem is in your development studio, you can fix it by running the package in 32 bit mode on your project:

1. Right-click your solution
2. Click 'Properties'
3. Select 'Debugging' node
4. Set 'Run64BitRuntime' property to False
5. Save and re-run your solution
6. Click Apply
7. Re-run your package

If the problem is in your SQL server put your package in a job and change the run time to 32bit:

1. Go to SQL Serve Agent option
2. Right click on Jobs and select 
3. Go to steps and click on New
4. Fill out the options with your package information
5. Click on configuration
6. Click on Advance
7. Check on 32 bit
8. Click on OK
9. Complete your job and run it

How to stop execution of SSIS Package from SSMS

If your package is running and you want to stop it from the SQL Management Studio follow this steps:

1.- Go to the Integration Services Catalogs and select the SSISDB catalog
2.- Go to the SSISDB catalog Right Click and select the “Active Executions”
3.- You will see the currently running packages, select the desired package then click STOP

How to connect to SQL database inside of Script Task in SSIS using C#

This example shows how to execute a store procedure inside of a SSIS Script Task:

// Get the connection from the Dts package

Microsoft.SqlServer.Dts.Runtime.ConnectionManager cm = Dts.Connections["connectionManaget1"];

// Create the connection

System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

// Create the command with the connection

System.Data.SqlClient.SqlCommand sqlComm = new System.Data.SqlClient.SqlCommand("EXEC dbo.MyStoreProcedure @var = 1", sqlConn);

// Execute the command

sqlComm.ExecuteNonQuery();

// Release the connection

cm.ReleaseConnection(sqlConn);

SSIS - Connection manager working on preview but no working when running package

If you are trying to pull data and the connection manager is working fine and you are able to preview the tables from the database, but when you run the package it fails, the problem is the driver used to connect, it doesn't support 64 bit or you don't have a 64 bit installed.

You probably get errors like:

• Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR.  The requested OLE DB provider OraOLEDB.Oracle.1 is not registered -- perhaps no 64-bit provider is available.
• [Execute SQL Task] Error: Failed to acquire connection "NADCWDDBS01A_INS01A.LegalReview". Connection may not be configured correctly or you may not have the right permissions on this connection.
• Etc.

To solve the problem:

1. Go to your SSIS project properties
2. From Configuration properties select Debugging
3. On the Data Flow Optimizations change the option Run64BitRuntime to false

Error Starting Script Editor in SSIS 2005

If you are having a problem launching the design script from the script task editor in business intelligence development studio 2005, the solution is installing the "Microsoft SQL Server 2005 Service Pack 3 (KB955706)", this update fixes this issue.

This is the link to download the Microsoft SQL Server 2005 Service Pack 3 (KB955706)