SQL Server Performance

trying to change data when importing

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Aug 31, 2006.

  1. SQL_Guess New Member

    Hi all,<br /><br />Having some fun with an excel import.<br /><br />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.<br /><br />That is all working now.<br /><br />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.<br /><br />2 of the columns I receive have either nothing, or X in them. The columns they go into are defined as BIT, NOT NULL.<br /><br />So, in SQL it would be something relatively simple like:<br /><br />CASE<br /> When IsAvailable = 'X' then 1<br /> When IsAvailable is null then 1<br /> ELSE 0<br />end<br /><br />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.<br /><br />to outline my current solution:<br />---- table 1 = this all works -------------<br />Excel Source --&gt; MultiCast (For Table 1)--&gt;Data conversion for table1--&gt;<img src='/community/emoticons/emotion-7.gif' alt=':S' />ort for Table1--&gt; Merge Join for table 1 (left Outer join) as 'left' leg<br />Table1 Source --&gt; Sort Table1 --&gt; Merge Join for table 1 (left Outer join) as 'right' leg<br /><br />Merge Join for table 1 --&gt; Conditional Lplit for table1<br />Conditional Split for table1 (table1 source PK is null) --&gt;Insert Into Table1 Destination<br />Conditional Split for table1 (table1 source PK is not null) --&gt;Update Table1 OLE DB Command<br />---- table 2 = this needs to be able to convert X/NULL to BIT -------------<br />MultiCast (For Table 2)--&gt;Copy Column for Table2 --&gt;Data Conversion for Table 2--&gt;table3 lookup to get FK--&gt;Sort for Table2 merge--&gt;Merge Join for table 2 (left outer join) as 'left' leg<br />Table2 Source --&gt; Sort Table2 --&gt; Merge Join for table 2 (left outer join) as 'right' leg<br /><br />Merge Join for table 2 --&gt; Conditional split for table 2<br />Conditional split for table 2(table2 source PK in null) --&gt;insert into table 2<br />Conditional split for table 2(table2 source PK in not null) --&gt;update table 2 ole db command<br />-----------------------------------------<br /><br />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.<br />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.<br />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.<br />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:<br />-------------.net script code-------------<br />Imports System<br />Imports System.Data<br />Imports System.Math<br />Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper<br />Imports Microsoft.SqlServer.Dts.Runtime.Wrapper<br />Imports Microsoft.SqlServer.Dts<br /><br />Public Class ScriptMain<br /> Inherits UserComponent<br /><br /> Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)<br /> If Not (Row.EndOfRowset) Then<br /> If (Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X")) Then<br /> Row.IsDotComVanEnabled = "1"<br /> End If<br /> If (Row.IsStoreCollectionEnabled_IsNull) Or (Row.IsStoreCollectionEnabled.Contains("X")) Then<br /> Row.IsStoreCollectionEnabled = "1"<br /> End If<br /> Row.NextRow()<br /> End If<br /><br /> End Sub<br /><br />End Class<br />------------------------------------------<br />I also tried it like this:<br />----------------1st draft .Net script code--------<br />Imports System<br />Imports System.Data<br />Imports System.Math<br />Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper<br />Imports Microsoft.SqlServer.Dts.Runtime.Wrapper<br />Imports Microsoft.SqlServer.Dts<br /><br />Public Class ScriptMain<br /> Inherits UserComponent<br /><br /> Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)<br /> If (Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X")) Then<br /> Row.IsDotComVanEnabled = "1"<br /> End If<br /> If (Row.IsStoreCollectionEnabled_IsNull) Or (Row.IsStoreCollectionEnabled.Contains("X")) Then<br /> Row.IsStoreCollectionEnabled = "1"<br /> End If<br /><br /> End Sub<br /><br />End Class<br /><br /><br />-------------------------------------------------<br /><br />Those both threw the following error;<br /><br />-------------error information-----------------<br />Script Component has encountered an exception in user code:<br />PROJECT NAME: ScriptComponent_a333140d269b413bb1bddef390da7e16<br />Object reference not set to an instance of an object.<br /><br /> at ScriptComponent_a333140d269b413bb1bddef390da7e16.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)<br /> at ScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)<br /> at xScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)<br /> at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)<br />-----------------------------------------------<br /><br />Any ideas?<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  2. Madhivanan Moderator

    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

Share This Page