November 7, 2016 at 3:26 am
Heya All,
SSIS 2008 R2 (Data and Structure are made up)
I am importing data into a Products tables. The table has two fields, Product Name and Active Date.
CREATE TABLE Product (ProductName VARCHAR(20), Active DATETIME DEFAULT GETDATE())
If the import row contains an active date it should be used, if not the default value should 'kick in'.
I prefer not to duplicate data rule logic if at all possible. Therefore the package needs to accommodate for both these inserts:
INSERT dbo.Product ( ProductName) VALUES ('Product A')
INSERT dbo.Product ( ProductName, Active) VALUES ('Product B','2000-01-01')
I am using an OLEDB Destination, INSERTS are provided just as an example
In the past I have used a Conditional Split to create the two inserts above, i.e. two destinations, however I am coming across tables with multiple default values which makes this approach that much more complicated.
Is there any other way to handle default values?
I should add, would like to avoid a staging table and dynamic sql.
Thanks
Steve
November 7, 2016 at 4:06 am
Steve
Try this:INSERT dbo.Product ( ProductName) VALUES ('Product A', DEFAULT)
Presumably you know at design time which columns have default values? In that case, use a Derived Column transformation to change NULL or blank values to DEFAULT.
Why do you want to avoid staging tables and dynamic SQL, by the way??
John
November 7, 2016 at 4:25 am
Hi John,
Thanks for the response.
Sorry for being dense but how would I assign a value of DEFAULT? I probably should have been more clear, I am using OLE Destination for the inserts so not actually building the insert statement.
As for staging tables - I try to avoid, where possible, additional reads from/writes to the database. I do use them but only as a last resort. Mostly for bulk updating.
As for dynamic SQL - For anything but basic SQL I find them just a nightmare to manage/debug. Also I have an OOP background and while dynamic SQL is flexible it just has a 'wrongness' feel about it to me. This is just a personal opinion. If I ever came across a scenario where I have to use it I will.
Thanks again
Steve
November 7, 2016 at 4:45 am
Steve
I see. I assumed that because you posted INSERT statements, you were actually using them in your package. I don't know how SSIS data flows handle default values. If your columns with defaults are NULLable, you could play around with the KeepNull (it's called something like that) setting in your data flow so that if a NULL hits the column, the default value is inserted. Apart from that, the only options I can think of involve building INSERT statements on the fly or using a staging table. Staging tables do indeed have a small IO cost, but in many cases they save other resources by simplifying processing.
John
November 7, 2016 at 4:52 am
Thanks again John
I included the insert because I have been asked in the past to provide the data structures/processes. My bad for leaving out that I was using OLEDB Destination
Cheers
Steve
November 7, 2016 at 4:54 am
Here is a possible solution using a trigger
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Product05') IS NOT NULL DROP TABLE dbo.Product05;
CREATE TABLE dbo.Product05
(
ProductName VARCHAR(20) NOT NULL
,Active DATETIME NOT NULL DEFAULT GETDATE()
);
GO
CREATE TRIGGER dbo.TRG_PRODUCT05_INSERT_DEFAULTS
ON dbo.Product05
INSTEAD OF INSERT
AS
INSERT INTO dbo.Product05(ProductName,Active)
SELECT
I.ProductName
,ISNULL(I.Active,GETDATE())
FROM inserted I
GO
INSERT INTO dbo.Product05(ProductName,Active)
VALUES ('ONE',NULL)
,('TWO',GETDATE()- 1);
SELECT
P.ProductName
,P.Active
FROM dbo.Product05 P;
Output
ProductName Active
-------------------- -----------------------
ONE 2016-11-07 11:53:37.373
TWO 2016-11-06 11:53:37.373
November 7, 2016 at 5:06 am
Hi Eirikur
Unfortunately I have no control over the data structure so is not be an option for me.
Interesting idea though. Quick followup, if I had that as a trigger, would I still need need the DEFAULT value? I try to aviod duplication of the 'data rule' logic, e.g. the GETDATE() is in multiple places
Thanks
Steve
November 7, 2016 at 5:17 am
SteveD SQL (11/7/2016)
Hi EirikurUnfortunately I have no control over the data structure so is not be an option for me.
Interesting idea though. Quick followup, if I had that as a trigger, would I still need need the DEFAULT value? I try to aviod duplication of the 'data rule' logic, e.g. the GETDATE() is in multiple places
Thanks
Steve
Unfortunately the DEFAULT keyword doesn't work inside the trigger as an insert value so you would have to set the actual value in the trigger, in this case using getdate.
😎
November 7, 2016 at 5:22 am
Sorry, again not being clear.
By DEFAULT I meant the default value declaration for the column. Wouldn't it just be handled by the trigger? In otherwords could I just have...
CREATE TABLE dbo.Product05
(
ProductName VARCHAR(20) NOT NULL
,Active DATETIME NULL
);
.. and let the trigger handle the 'default' value
November 7, 2016 at 5:24 am
Take a look at this link[/url]. You'll see that checking the Keep Nulls option, in conjunction with table-level default constraints, should get you what you need.
Alternatively, if you cannot set the necessary constraints, and as John has already mentioned, use derived columns to achieve the desired functionality.
--Edit: grammar fix
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 7, 2016 at 5:57 am
SteveD SQL (11/7/2016)
I included the insert because I have been asked in the past to provide the data structures/processes.
Steve
Yes, you were right to do so. I just made the wrong assumption.
Phil Parkin (11/7/2016)
Take a look at this link[/url]. You'll see that checking the Keep Nulls option, in conjunction with table-level default constraints, should get you what you need.
Phil
I haven't actually tried that, but I read somewhere that it works fine if your columns allow NULLs, but gives and error if they don't. That may or may not be right, but it's certainly something for Steve to bear in mind when testing.
John
November 7, 2016 at 6:08 am
John Mitchell-245523 (11/7/2016)
SteveD SQL (11/7/2016)
I included the insert because I have been asked in the past to provide the data structures/processes.Steve
Yes, you were right to do so. I just made the wrong assumption.
Phil Parkin (11/7/2016)
Take a look at this link[/url]. You'll see that checking the Keep Nulls option, in conjunction with table-level default constraints, should get you what you need.Phil
I haven't actually tried that, but I read somewhere that it works fine if your columns allow NULLs, but gives and error if they don't. That may or may not be right, but it's certainly something for Steve to bear in mind when testing.
John
Notice I used the word 'should' ... that's because I haven't tried it either 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 7, 2016 at 6:10 am
In doing some experimenting un-checking 'Keep Nulls' gives me what i need if the field is NULLable as John mentioned. The problem is the field isn't, i.e. it should never be null.
Thanks
Steve
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply