Displaying NULL Values In The Destination Table

Sep 17, 2007

Dear Members,

I extracted data from a flat file using SSIS package and load them into destination table. One column contains no data. Data type of that column is varchar(9). I want to display "NULL" values in that column.

I used LOOK up transformation and used the following query.

SELECT PassThroughRouting =
CASE PassThroughRouting
WHEN 'NULL' THEN 'NULL'
END
FROM EPICWareTable

But it gives a error message.

Does any body can help me?

Thanks

View 9 Replies

Displaying Null Values

Apr 28, 2008

I have a set of data for days of a Month , But on certain days there are no values recorded, I still would like to display them in my result set as NULL (if text) and 0 (If Int / Real). Can anyone please suggest suitable solution.

View 11 Replies View Related

Displaying The Values That Dont Match And Replacing By NULL

Feb 6, 2003

Hi,

Im having a couple of problems with SQL.

I have this query...

SELECT Band.Name, Member.Name
FROM Member
JOIN MemberOf ON Member.Mid = MemberOf.Mid
JOIN Band On MemberOf.Bid = Band.Bid
WHERE MemberOf.Instrument = 'keyboards';

which basically shows me the name of the bands who had keyboard players. I would like it to also display the names of the band who didnt have keyboard players, replacing the keyboard players name with "NULL".

so... my questions are :)

how do u get it to display the records that do not match the condition, and how do u get it to replace the keyboard players name with "NULL" when they do not match the condition.

PS. The three tables are

Band. Which has Bid as a primary key.
Member. Which has Mid as a primary key.
MemberOf. Which links these through its two foreign keys Mid and Bid.

Thanks for your help! (assuming someone does)

View 2 Replies View Related

Null Values In Destination

Oct 11, 2006

Hi to all!
I created a simple package �� data flow task, containing OLE DB Source, with Data Access Mode �� SQL command and OLE DB Destination.
My SQL command is

update support_incident
set date_closed = rn_edit_date
where status_text = 'Closed' and date_closed is null
DECLARE @CMonth as datetime
DECLARE @LMonth as datetime
SET @CMonth = '10/1/2006'
SET @LMonth = DateAdd (m, -1, @CMonth)
select
@LMonth as Reporting_Date,
(select count(support_incident_id) from support_Incident where rn_create_date >= @LMonth and rn_create_date < @CMonth) as Opened,
(select count(support_incident_id) from support_Incident where Date_Closed >= @LMonth and Date_Closed < @CMonth) as Closed,
(select count(support_incident_id) from support_Incident where rn_create_date < @CMonth and (Date_Closed >= @CMonth or Date_Closed is null)) as Backlog,
(select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @CMonth and (Date_Closed >= @CMonth or Date_Closed is null))) as Critical_Backlog,
((select count(support_incident_id) from support_Incident where rn_create_date < @CMonth and (Date_Closed >= @CMonth or Date_Closed is null)) - (select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @CMonth and (Date_Closed >= @CMonth or Date_Closed is null)))) as Operational_Backlog
from support_incident
where support_incident_code = '1000'

The SQL statement is successfully parsed and I can see the columns and numbers in Preview Query Results. The package is executed without errors and warnings with success. In Destination server, the table is created with columns according to query, but the problem is no numbers are written in rows, all values are nulls.

I can��t figure out what is the reason. Can anybody to help me?

Thank you.

View 13 Replies View Related

After SSIS Package Runs All Rows, All Fields Are NULL In Destination Table ???

Jul 21, 2007

I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.

I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.

Does anyone have a clue as to why it would be doing this?

Any help would be much appreciated.

Thanks...

View 1 Replies View Related

SQL 2012 :: SSRS Report Not Displaying Time Values Correctly From Server Table

Feb 26, 2015

I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.

SQL Server table time value shown in milliseconds:
00:00:00.82

Reporting Services report value shown:
00:00:00

View 3 Replies View Related

Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform

Jan 25, 2008

I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?

-- Ryan

View 7 Replies View Related

What's The Accepted Way To Retrieve Records In A SQL Table With Null Values Using A Visual Studio 2005 Table Adapter?

Jan 21, 2008

I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
I have a very simple dilemma. In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null> (accounting for about 95% of the records in the database). Thanks in advance Simon

View 9 Replies View Related

Alter Table Datatype With Null Values

May 7, 2015

I am trying to change a column from a decimal(18,2) to a decimal(18,3). What is the SQL command to alter this table?

SQL Command:

alter table TableName
alter column ColumnName decimal(18,3) [null]

the above command is right ?

View 6 Replies View Related

Can't Import An Access Table Due To Null Values.

Jul 20, 2005

Question: Why would I not be able to import an Access 97 table inwhich some records have null values in fields that allow null values?Wouldn't the table's design be imported first, bringing the columns'"allow nulls" attribute with it?I'm dealing with both text and numeric columns. Not all columnscontaining nulls cause an error.Thanks,Bob C.

View 2 Replies View Related

.NET Framework :: CLR Table Function With Null Values

Jun 23, 2015

I'm trying to do a Clr function wiht null values but I have an error. My Clr is like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Text;

[code]...

A .NET Framework error occurred during execution of user-defined routine or aggregate "function":

System.InvalidCastException: La conversión especificada no es válida.
System.InvalidCastException:
en Microsoft.SqlServer.Server.ValueUtilsSmi.GetSqlInt16(SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)
en UserDefinedFunctions.function()

View 5 Replies View Related

Filtering Out Null Values On A Matrix Table

Jan 29, 2008

Hi,

How do I filter out Null values in a matrix table

one off my columns is picking up the Null values and I would like to filter this out.

Is there a simple way of doing this as filtering it out at the Dataset level is not the ideal solution for me.

thanks

View 5 Replies View Related

1) Null Value In Foreign Key 2) Extra Value To Indicate All The Values In PK Table Apply

Feb 27, 2007

I have two tables 1) tblCustomer (ID, Name, City) 2) tblemp (ID, NAME, Dept.ID, tblcustomer.ID)
Both the tables have ID as PKA emp can be either assigned a) All customers b)single customer c) NO customer
Pls note:- there will never be 2 or 3 customer linked to emp (my actuall requirement tables are different but to explain i am using the above tables)
I know how to assign single customer......but had problem how to link all customers and "no customer"
Please tell me if the following solution is right?1) I will manually insert a record in tblCustomer with id 0 as " all customers" and will not allow the user to delete it 2) store null in FK if it is "no customer"
Also please tell me is it ok to store one more value in tblCustomer as -1 and take it as "No customer"
I have a DDL in the Employee page which should be displaying the names of allthe customers with 2 extra values "ALL CUSTOMERS" and "SELECT" Select is the default value in DDL which says "NO CUSTOMER" selected yet
Thank youSara

View 10 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)

[Code]...

View 2 Replies View Related

Inserting Few Recordsets Into Temp Table Containing NULL Values

Jul 20, 2005

HiI need some help on achieving the following:I wrote a querie which collects Data out of three Tables, the Resultlooks like this:SET NOCOUNT ONDECLARE @ROWINTDECLARE @CURPTNO CURSORSET @CURPTNO = CURSORFORSELECT * FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGINSELECT ONE.CYNO, ONE.ROLE, ONE.ROL_BEZ, ONE.PTNO, ONE.NOCY, TWO.TEXTAS NOCY_TEXTFROM(SELECT CY.CYNO,RE.CYNO AS RECYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW)AND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO IN(@ROW))) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOThe Result looks like this:RS1:6313,1300,Architekt,99737505,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLRS2:2693265,100,Bauherr,99756900,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pl�ne selberRS3:2691919,100,Bauherr,99755058,NULL,NULL2691962,6000,Kontakt,99755058,NULL,NULLMy Problem is, that I need to have all the Resultsets in one Table atthe end.So my further undertaking was to create a Temp Table with theexpectation to receive all the resultsets in one Step.The TSQL for this looks like that:SET NOCOUNT ONCREATE TABLE #CYRE_TEMP(CYNOINT NULL,ROLEINT NULL,ROL_BEZVARCHAR (60) NULL,PTNOINT NULL ,NOCYINT NULL,TEXTVARCHAR (60) NULL)GODECLARE @CYNOINT,@ROLEINT,@ROL_BEZVARCHAR (60),@PTNOINT,@NOCYINT,@TEXTVARCHAR (60),@ROWINT,@CURPTNOCURSORSET @CURPTNO = CURSOR FORSELECT PTNO FROM TEMPOPEN @CURPTNOFETCH NEXT FROM @CURPTNOINTO @ROWWHILE (@@FETCH_STATUS = 0)BEGININSERT INTO #CYRE_TEMP (CYNO, ROLE, ROL_BEZ, PTNO, NOCY, TEXT)VALUES(@CYNO,@ROLE ,@ROL_BEZ ,@PTNO ,@NOCY ,@TEXT)SELECT @CYNO = ONE.CYNO,@ROLE = ONE.ROLE,@ROL_BEZ = ONE.ROL_BEZ,@PTNO = ONE.PTNO,@NOCY = ONE.NOCY,@TEXT = TWO.TEXTFROM(SELECT CY.CYNO, RTRIM(RE.ROLE) AS ROLE, RTRIM(V_ROLE.TEXT) ASROL_BEZ,RE.PTNO AS PTNO, RTRIM(RE.NAME) AS SACHBEARBEITER, RE.NOCYFROM CYRIGHT OUTER JOINRE ON RE.CYNO = CY.CYNOINNER JOINV_ROLE ON RE.ROLE = V_ROLE.CODEWHERE (RE.PTNO IN(SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROWAND V_ROLE.LANGUAGE = PT.CLANG))) AS ONELEFT JOIN(SELECT V_NOCY.CODE, V_NOCY.TEXTFROM V_NOCYINNER JOINPT ON PT.CLANG = V_NOCY.LANGUAGEAND PT.PTNO IN (SELECT PT.PTNOFROM PTWHERE PT.PTNO =@ROW)) AS TWOON ONE.NOCY = TWO.CODEFETCH NEXT FROM @CURPTNOINTO @ROWENDCLOSE @CURPTNODEALLOCATE @CURPTNOSELECT * FROM #CYRE_TEMPDROP TABLE #CYRE_TEMPGOAnd the Output looks like this now:Q1:NULL,NULL,NULL,NULL,NULL,NULL2392762,100,Bauherr,99737505,NULL,NULLNULL,1,Planer,99756900,2,Bauherr macht Pl�ne selberCan someone help me getting all the 6 Rows into one Table as Output?I appreciate any available Help on this..Ssscha

View 1 Replies View Related

Checking For Null Values In A Table/Matrix Cell

Jun 12, 2007

What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?

View 3 Replies View Related

Error Executing A Sql Statement Against A Table With Null Date Values

Apr 10, 2008

We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:

"Error converting data type DBTYPE_DBTIMESTAMP to datetime."

View 3 Replies View Related

Data Flow Task - Have Null Values Take Table Default

Jun 6, 2006

Hi,

I have a data transform from a flat-file to a SQL server database.
Some of the flat-file fields have NULL values. The SQL table I'm
importing into does not allow NULL values in any field, but each field
has a Default value specified.

I need to have it so that if a null value comes across in a field using
the data transform, it takes the table default on import. I could of
sworn I had this working a few days ago, but I get errors now that
state I'm violating table constraints. Has anyone done this before?

Thanks
Jeff

View 5 Replies View Related

Transact SQL :: Preserve And Return NULL For Non Matching Values From A Table Valued Function

Jun 29, 2015

I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.

When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.

SELECT A.CID,
A.ANI,
A.PID,
B.RD
FROM T1 AS A CROSS APPLY GIVERD(A.PID) B

CREATE TABLE [DBO].[RDTEST](
[PID] [INT] NULL,
[RD] [INT] NULL
)

[Code] ....

View 4 Replies View Related

MIN Date Without Displaying NULL Value

Sep 24, 2013

I have 3 dates one of them could be NULL . I do not want my min to display it as NULL.Below is my script. Is there a way I could do it?

select Order_id, customer_id, date_1, date_2, date_3
into #temp1
from ##Temp_dates (nolock)
Select Case when (date_1 < date_2 and date_1< date_3) then date_1
when (date_2< date_1 and date_2< date_3) then date_2

[code]...

View 4 Replies View Related

Transact SQL :: How (NULL) Value Is Displaying After Encrypting A Stored Procedure

Oct 30, 2015

How 'NULL' value is displaying after encrypting a stored procedure? links to know the procedure behind this how the encrypted procedure is storing and updating with NULL value under the same column after the encryption.

View 7 Replies View Related

Displaying Values On A Chart

Nov 21, 2007

I have a chart with columns on it, and my boss wants me to display the height of each column on the chart.

Is there a way to do this? I've googled it some, and searched around in the chart properties, but I couldn't find anything.

View 1 Replies View Related

Values Displaying As A Percent

Nov 15, 2007

Hello,

This is my issue. I have values in my table that I need to format as a percent. So when I use the FormatPercent() function, it works but if one of the records has no value for that field. It generates a #Error message in that textbox. I tried using an if statement and that didn't work either. Someone please help me with this. Any information is appreciated.

View 17 Replies View Related

NULL Values Returned When Reading Values From A Text File Using Data Reader.

Feb 23, 2007

I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?

View 12 Replies View Related

Displaying Variable Values After Execution

Mar 13, 2007

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

View 2 Replies View Related

Displaying Values For Multi-value Parameter

Dec 15, 2007

At the top of my report, if I want to display the selected value for a given
parameter, I can put this expression in a text box:

="The xyz param's value is: " & Parameters!paramXYZ.Label(0).ToString

How do I go about displaying all the selected values for a multi-value
parameter (one that has mulitple checkboxes in the drop-down list)? Is it
possible to do a for loop and iterate through the values of the param within
an expression? Something like

for i = 0 to Parameters!paramXYZ.Count - 1
mystring = mystring & ", " & Parameters!paramXYZ.Label(i).ToString

And then display mystring in the textbox? Or can I use a list control on the
report?

Thank you,

View 4 Replies View Related

Integration Services :: SSIS Reads Nvarchar Values As Null When Excel Column Includes Decimal And String Values

Dec 9, 2013

I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?

CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]

View 5 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.

If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If

Any help welcome.

View 1 Replies View Related

Displaying Multiple Values In A Concatenated String

Oct 17, 2007

SQL Server 2005.(SP2). MS SSRS;
I want to display some numbers in the same line as a concatenated string. For example a Customer may have multiple bills. These bill numbers are displayed in separate rows. I want to display them all on the same line.
Example of current display:
Customer Bill #
ABC Company 123
ABC Company 456
ABC Company 789 etc

I want this to display as below:
Cusotmer Bill #
ABC Company 123, 456, 789, etc.

Is this possible in SSRS. Please help me with the syntax.

Thanks in advance.

View 2 Replies View Related

SSIS Tries To Insert NULL Value Into A Destination Column Ignored In The Mapping.

Jan 12, 2008

Hello everyone,

I am having a little problem with a simple package and I do not know if this is a known issue or that I am missing something.

I have a data flow task, a simple one, with an oledb source pulling data, using a select statement, from a sql server 2005 instance, and an ole db destination pointing to a table in a sql server 2000 instance. Both intances are standard edition. The table in the destination has a column which allow null values and has also a default constraint (getdate()), and this column is not present in the source. When I map the columns in the destination, I leave this column as "ignore", not being mapped to any column from the source. The problem is that when I execute the task, SSIS is trying to insert NULL value into this column, so the package fail with the error "can not insert NULL value into column myColumn". I wonder why is it trying to insert NULL value if the column is not mapped to any column from the source.

Is this a known issue or I am nissing something in the settings?

If the destination table has rowversion or identity columns, there is no problem ar all. I ignore those columns in the mapping and SQL Server feeds them as expected.

Thanks in advance,
Alejandro Mesa

View 19 Replies View Related

Use Order By But Want NULL Values As High Values

Nov 9, 2000

Hi,

My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list

e.g.

Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -

At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?

Thanks
Dan

View 1 Replies View Related

SSIS Script Source Destination Insert Null Columns

Dec 1, 2006

Hi I have an SSIS package that retrieves table data from a database using an OLE DB source component.

This then passes the rows of data to a script destination component.

Within this script my code takes the row data and inserts it into the database (amongst other things).

However I have noticed that if any of the Row columns contain nulls then the component falls over with errors when run like so:

[Back Up Prize Banks [2875]] Error: System.Data.SqlClient.SqlException: Parameterized Query '(@PASId int,@ScriptName nvarchar(17),@LastModified datetime,@Pri' expects parameter @RegenerateXML, which was not supplied. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

I have solved the problem by checking the column contents before inserting it as follows:

Before:
Command.Parameters.AddWithValue("@RegenerateXML", Row.RegenerateXML)

After:
If (Row.RegenerateXML_IsNull()) Then
Command.Parameters.AddWithValue("@RegenerateXML",System.DBNull.Value)
Else
Command.Parameters.AddWithValue("@RegenerateXML", Row.RegenerateXML)
End If

Which is great but it does turn 1 line into 5 lines. I have 20 parameters which takes up 20 lines of code which will now be 20 x 5 = 100 lines of code.

My question is :

Is there a better way to write this code without having to take up so many lines?

Thanks

Matt.

View 2 Replies View Related

OLE DB Destination - Obtaining Identity Values

Apr 29, 2008

Is there any way to get the new identity values created after an OLE DB Destination has finished its inserts?

Basically my problem is this...

My source database has its primary and foreign key columns as a GUID datatype. In my conversion, my destination database will be using INT (identity) columns for primary/foreign keys. How do I propagate the foreign key relationships to the destination database in an SSIS package? So obviously I can use an OLE DB Destination to generate the data with identities for the parent table, but after that I am stuck in how to create the relationships for the child tables.

I know how to do this in TSQL fairly easily (cursor or temp tables), but it doesn't seem so simple in SSIS.

thanks in advance.

View 9 Replies View Related