SQL Server Performance Forum – Threads Archive

trying to change data when importing

Hi all, Having some fun with an excel import. I have a excel sheet that has data that needs to be UpSert’ed into 2 different tables. In addition, I need to use a value in the spreadsheet to determine the PK from a reference data table, for one of the UpSert oprations. That is all working now. The thing I’m struggling with is something I am sure is quite simple – but I’m not seeing a solution from attempts, googling or BOL. 2 of the columns I receive have either nothing, or X in them. The columns they go into are defined as BIT, NOT NULL. So, in SQL it would be something relatively simple like: CASE When IsAvailable = ‘X’ then 1 When IsAvailable is null then 1 ELSE 0 end But I don’t know how to do this to data that was in a spreadsheet, and now is a resultset being handed from a task to another task. to outline my current solution: —- table 1 = this all works ————- Excel Source –> MultiCast (For Table 1)–>Data conversion for table1–><img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />ort for Table1–> Merge Join for table 1 (left Outer join) as ‘left’ leg Table1 Source –> Sort Table1 –> Merge Join for table 1 (left Outer join) as ‘right’ leg Merge Join for table 1 –> Conditional Lplit for table1 Conditional Split for table1 (table1 source PK is null) –>Insert Into Table1 Destination Conditional Split for table1 (table1 source PK is not null) –>Update Table1 OLE DB Command —- table 2 = this needs to be able to convert X/NULL to BIT ————- MultiCast (For Table 2)–>Copy Column for Table2 –>Data Conversion for Table 2–>table3 lookup to get FK–>Sort for Table2 merge–>Merge Join for table 2 (left outer join) as ‘left’ leg Table2 Source –> Sort Table2 –> Merge Join for table 2 (left outer join) as ‘right’ leg Merge Join for table 2 –> Conditional split for table 2 Conditional split for table 2(table2 source PK in null) –>insert into table 2 Conditional split for table 2(table2 source PK in not null) –>update table 2 ole db command —————————————– Now, if I correct the spreadsheet to have 0’s and 1’s in the two column, then the process above works. But I cannot (yet) force business to do that. If tried to use SQL Command for the excel source, but there is limited functionality on the command – I cannot do SQL coalese, isnull or case statements, which would allow me to resolve that data at source. I’ve tried to use derived columns to alter the columns. I think that the REPLACE (IsAvailable, VariableContainingX,VariableContaining1) might work to change X’s to 1, but that doesn’t resolve the NULL issue. I’ve tried to use a script component to handle the conversion – which REALLY feels like a bad way to do this – the .Net script is wrote was: ————-.net script code————- Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports Microsoft.SqlServer.Dts Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Not (Row.EndOfRowset) Then If (Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X")) Then Row.IsDotComVanEnabled = "1" End If If (Row.IsStoreCollectionEnabled_IsNull) Or (Row.IsStoreCollectionEnabled.Contains("X")) Then Row.IsStoreCollectionEnabled = "1" End If Row.NextRow() End If End Sub End Class —————————————— I also tried it like this: —————-1st draft .Net script code——– Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports Microsoft.SqlServer.Dts Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If (Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X")) Then Row.IsDotComVanEnabled = "1" End If If (Row.IsStoreCollectionEnabled_IsNull) Or (Row.IsStoreCollectionEnabled.Contains("X")) Then Row.IsStoreCollectionEnabled = "1" End If End Sub End Class ————————————————- Those both threw the following error; ————-error information—————– Script Component has encountered an exception in user code: PROJECT NAME: ScriptComponent_a333140d269b413bb1bddef390da7e16 Object reference not set to an instance of an object. at ScriptComponent_a333140d269b413bb1bddef390da7e16.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at ScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at xScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) ———————————————– Any ideas? Panic, Chaos, Disorder … my work here is done –unknown

1 You can change the source EXCEL file to have proper data
2 Import data to staging table and then insert proper data to target table Insert into stagingtable(cols,IsAvailable) from Openrowset(..) Insert into targettable(cols,IsAvailable)
Select cols,CASE WHEN IsAvailable is null or IsAvailable=’X’ then 1 else 0 END from stagingtable 3 or do this when you select data from EXCEL
eghttp://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8440 Madhivanan Failing to plan is Planning to fail

]]>