SSIS Question - 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 2012 Forums

SSIS and Import/Export (2012)

SSIS Question

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2015-04-20 : 11:10:49

Hi Guys,

Here is my source data,

FName,LName,State
Frank,Smith,NY
Carla,James,Null
Norman,Sa,Null
Frank,G,CA
Chris,U,Null

How can I accomplish in above data through SSIS, Here is my destination look like.

FName,LName,State
Frank,Smith,NY
Carla,James,NY
Norman,Sa,NY
Frank,G,CA
Chris,U,CA

Here what I want, Start reading State Column from Top and pick that value and give the value until State field is not Null.

Please Help. I want this in SSIS, T-SQL is my last Option and my source is .xlsx FILE.

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-21 : 08:56:07

I did it this way in the Data Flow Task:

1. Excel Source Component
2. Script component to replace nulls
3. OLEDB Destination component to write results to SQL.

Configure the script component, selecting only the column State as usage type ReadWrite
The Script component only has a few lines at the Input0_ProcessInputRow method:


    public string currentState { get; set; }
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.State_IsNull || Row.State.ToLower() == "null")
            Row.State = currentState;
        currentState = Row.State;
    }

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources