Hello, I'm using SQL sder er 2008 Standard/ I am trying to use a script component to do a lookup, but when I execute the package (in debugging mode), In get an error screen. Part of the error description is in Dutch, but it says "Object reference not set to an instance of an object " More detailed, from the debug output screen: Error: 0xC0047062 at Data Flow Task, Script Component 1 [1518]: System.NullReferenceException: Object reference not set to an instance of an object . at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper) Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "Script Component 1" (1518) failed the pre-execute phase and returned error code 0x80004003. This is the script: using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Data.SqlClient; using System.Collections.Generic; public struct RangeItem : IComparable<RangeItem>, IComparable<DateTime> { public Int32 Key; public Int32 Value; public RangeItem(Int32 key, Int32 value) { Key = key; Value = value; } #region IComparable Members public int CompareTo(RangeItem other) { return Key.CompareTo(other.Key); } public int CompareTo(DateTime other) { return Key.CompareTo(other); } #endregion } [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { // Variables SqlConnection connection = null; Dictionary<String, List<RangeItem>> Cache = new Dictionary<String, List<RangeItem>>(); const string query = "select WerkID, MedewerkerID, Werk_eind_KEY from DimMedewerker"; public override void PreExecute() { base.PreExecute(); connection = (SqlConnection)this.Connections.Connection.AcquireConnection(null); if (this.Variables.PreCache) { FillCache(); } } private void FillCache() { FillCache(null); } private List<RangeItem> FillCache(string key) { string sql = query; if (!string.IsNullOrEmpty(key)) { sql += string.Format(" where MedewerkerID = '{0}'", key); } sql += " Order By MedewerkerID, Werk_eind_KEY"; SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); List<RangeItem> times = null; try { Int32 lastKey = 0; Int32 endKey = 0; while (reader.Read()) { Int32 WerkID = reader.GetInt32(0); Int32 MedewerkerID = reader.GetInt32(1); Int32 Werk_eind_KEY; if (reader.IsDBNull(2)) { Werk_eind_KEY = Int32.MaxValue; } else { Werk_eind_KEY = reader.GetInt32(2); } if (lastKey != MedewerkerID) { if (!string.IsNullOrEmpty(Convert.ToString(lastKey))) { // cache everything up until now times.Add(new RangeItem(Int32.MaxValue, endKey)); Cache.Add(Convert.ToString(lastKey), times); } lastKey = MedewerkerID; times = new List<RangeItem>(); } if (Werk_eind_KEY < Int32.MaxValue) { times.Add(new RangeItem(Werk_eind_KEY, WerkID)); } else { endKey = WerkID; } } times.Add(new RangeItem(Int32.MaxValue, endKey)); if (!string.IsNullOrEmpty(Convert.ToString(lastKey)) && times != null && times.Count > 0) { try { Cache.Add(Convert.ToString(lastKey), times); } catch (Exception e) { throw new Exception(lastKey + " " + times[0].Key + " " + times[0].Value, e); } } } finally { reader.Close(); } return times; } public override void PostExecute() { base.PostExecute(); } public override void Input0_ProcessInputRow(Input0Buffer Row) { Int32 MedewerkerID = Row.MedewerkerID; List<RangeItem> ranges = null; if (!Cache.TryGetValue(Convert.ToString(MedewerkerID), out ranges)) { // We don't have this value yet ranges = FillCache(Convert.ToString(MedewerkerID)); } if (ranges == null) { throw new Exception("Couldn't find value for MedewerkerID " + MedewerkerID); } int index = 0; if (ranges.Count > 1) { index = ranges.BinarySearch(new RangeItem(Row.ZiekDagKEY, 0)); if (index < 0) { index = ~index; } } try { Row.WerkID = ranges[index].Value; } catch (Exception e) { throw new Exception(string.Format("size: {0} index: {1} key: {2}", ranges.Count, index, Row.MedewerkerID), e); } } } What am I doing wrong? Regards, Hennie

Maybe you have a null reference somewhere. Make sure every variable that you use is explicitly declared and initialised.MCTS, MCITP - Please mark posts as answered where appropriate.

Check the pre execute block, for SQL connection you are passing null..

you can use one of following methods to debug your code in script component to troubleshoot it: http://microsoft-ssis.blogspot.co.nz/2011/04/breakpoint-does-not-work-within-ssis.html http://www.rad.pasfu.com

add some try catch blocks to indicate where it goes wrong. Combine that with the url Reza provided. I suspect its the pre execute...Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Hello Reza, I tried one of the debugging options (the first one), buit then I get: Error 1 Type 'ScriptMain' already defines a member called 'Input0_ProcessInputRow' with the same parameter types C:\Users\borrenbergs\AppData\Local\Temp\3\SSIS\4a0305c2acc8423c8b47a5c1422d4e79\main.cs 150 26 sc_030c4752ade24712ae7639503dd64964

Hi Hennie, It could be in these lines, probably connection is null: SqlCommand command = new SqlCommand(sql, connection); SqlDataReader reader = command.ExecuteReader(); List<RangeItem> times =null; Check the connection before using it: http://consultingblogs.emc.com/jamiethomson/archive/2005/10/10/SSIS-Nugget_3A00_-Verify-a-data-source-before-using-it.aspx Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File Automating/Silent Installation of BizTalk Deployment Framework using Powershell > Sending IDOCs using SSIS

The problem is either with the connection not established i.e. command.ExecuteReader this would fail with null reference or return type of execute reader is null i.e. reader.Read() will fail with null reference. http://btsbee.wordpress.com/

Hello Reza, I tried one of the debugging options (the first one), buit then I get: Error 1 Type 'ScriptMain' already defines a member called 'Input0_ProcessInputRow' with the same parameter types C:\Users\borrenbergs\AppData\Local\Temp\3\SSIS\4a0305c2acc8423c8b47a5c1422d4e79\main.cs 150 26 sc_030c4752ade24712ae7639503dd64964 debugging maybe time consuming in your case, you can try to create some file and write the values you want to see in that file File.AppendAllText(@"path", "variable value") inside the script task. You can put the values where you think maybe causing issues and check them one by one.regardsjoon

The problem is either with the connection not established i.e. command.ExecuteReader this would fail with null reference or return type of execute reader is null i.e. reader.Read() will fail with null reference. http://btsbee.wordpress.com/

..OK, so what are possible solutions? Regards, Hennie

I am looking into a similar issue where I cannot get a valid connection to use in my script task later If your issue matches mine the problem is caused by the following line of code returning a NULL and your "connection" variable ends up a NULL reference pointer. connection = (SqlConnection)this.Connections.Connection.AcquireConnection(null); The unfortunate part of my answer is that I have not yet been able to figure out how to convert, cast, or otherwise get the actual OleDB or SQL connection object from the this.Connections.Connection variable reference. If it was a Control Flow script task you would use the following code style to accomidate a OleDB connection type object (but this doesn't appear to work in the Data Flow script) ConnectionManager cm = Dts.Connections["name"]; IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as IDTSConnectionManagerDatabaseParameters100; OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection; Bob

regarding all solutions for this script to work, I want to know why you want to use the script? what is your requirement than cannot be done with built-in components? there might be better solution, so give us an overall summary of what you want to do and we try to provide the best solution for youhttp://www.rad.pasfu.com

I was not able to figure out how to get this to work with an OLEDB connection, but I was able to get it to work if I changed the OLEDB object to a .NET data provider based connection. Once a .NET data provider based connection it appears to work for a SQL Server connection, Oracle connection, or any other connection can be extracted using the previously listed code. connection = (SqlConnection)this.Connections.Connection.AcquireConnection(null); Unless there is a reason you have to use an ODBC based connection for this operation I recommend adding a .NET connection into your package (even if it is just for this task) and use that instead of ODBC. Hope that helps. Bob