Data transformation task - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Data transformation task

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-08 : 08:02:20

I have an excel spreadsheet with the columns:

StoreID ProductID ForceOn
--------------------------
101 123 y
101 456 n

I want to import into a table:

CREATE TABLE dotcomOnlyStoreStaging.ProductRangingList
(
  StoreID INT
, TPNB INT
, ForceOn BIT CONSTRAINT DF_ProductRangingList_ForceOn DEFAULT 1 --TRUE
)

In SQL, I would use a CASE statement. However, using a transformation task, which one enables you to change the values from a 'y' or 'n' into BIT values (use an expression as part of the transformation)?

Hearty head pats

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-09-08 : 08:28:14

Derived Column -

[ForceOn] == "y" ? 1 : 0

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-08 : 08:36:22

Bex, do yourself a favor and don't put transformational logic into your SSIS or DTS package.
Use SSIS/DTS solely for transferring data to a staging table and then kicking of a store procedure to verify, cleanse, transform, and load the data into production tables.
Your final solution will be much more robust, and much easier to debug and maintain, and much easier to upgrade to the next version of SQL Server.

Boycotted Beijing Olympics 2008

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-08 : 08:37:34

Thank you. Works perfectly!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2008-09-08 : 09:17:55

Hi Blindman

So in my example, dump all the data into a staging table using a simple bulk insert task:

CREATE TABLE staging.ProductRangingList
(
StoreID INT
ProductID INT
ForceOn CHAR(1)
)

And then write a procedure to insert into the persistance table:

CREATE PROCEDURE ProductRangingListInsert
AS
SET NOCOUNT ON

INSERT INTO persistance.ProductRangingList (StoreID, ProductID, ForceOn)
SELECT StoreID, ProductID, CASE FOrceOn WHEN 'Y' THEN 1 WHEN 'N' THEN 0 END
FROM staging.ProductRangingList

I agree in that it does seem a lot simpler/cleaner solution. Thank you

Hearty head pats

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-09-08 : 09:35:05

Yes, but you can do much more in your process.

You can add columns to the staging table to record the date and time the record was loaded and the filename of the source data.
You can also add a column for storing error messages for data that cannot be loaded for whatever reason. In your stored procedure you can write verification checks on the data and if there are any problems you can flag these records using the error column.

Boycotted Beijing Olympics 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources