Does Anyone know how you can copy values from a previous row into a null column in SSIS
-
I have been tasked with creating an upload of data from a report usingSSIS.
Loading the data is fine but the report has data in the following formatReference
Invoice Date
Amount
Start Date
End Date
A123456
01/05/2017
100.00
24/04/2017
26/04/2017
01/06/2017
100.00
24/05/2017
26/05/2017
01/07/2017
100.00
24/06/2017
26/06/2017
01/08/2017
100.00
24/07/2017
26/07/2017
Summary
400.00
B123456
01/05/2017
100.00
24/04/2017
26/04/2017
01/06/2017
100.00
24/05/2017
26/05/2017
01/07/2017
100.00
24/06/2017
26/06/2017
01/08/2017
100.00
24/07/2017
26/07/2017
Summary
400.00
The summary I am not interested in and am removing using a ConditionalSplit but does anyone know of a way I can then populate the blank referencecells with the parent value so the imported data will make sense.
i.e. the values written to the Database will beReference
Invoice Date
Amount
Start Date
End Date
A123456
01/05/2017
100.00
24/04/2017
26/04/2017
A123456
01/06/2017
100.00
24/05/2017
26/05/2017
A123456
01/07/2017
100.00
24/06/2017
26/06/2017
A123456
01/08/2017
100.00
24/07/2017
26/07/2017
B123456
01/05/2017
100.00
24/04/2017
26/04/2017
B123456
01/06/2017
100.00
24/05/2017
26/05/2017
B123456
01/07/2017
100.00
24/06/2017
26/06/2017
B123456
01/08/2017
100.00
24/07/2017
26/07/2017
Just getting back into SSIS after a 12 year break from SQL so all help is appreciated. -
Little confused by what your asking. It looks like, from your data, that you silple don't want to import the summary rows. If you use a conditional split to separate those rows out they won't be imported, thus there won't be any rows containing a NULL.I feel like we're missing an extra piece of information/data here.
Reread on the PC, and the table displayed in a better format, sorry.
Phil has the right idea here, a Script task is going to work well. Otherwise, if you had some kind of sequence number, you could put the data into a staging table, and then fill at the SQL level, but if those are all your columns, that's not going to be possible here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
I think you would need to use a Script Component Transformation to do this.
Here is an example of something similar. Have a read and then post back with any follow-up questions.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Andrew.Murdoch - Monday, November 6, 2017 6:44 AM
You haven't said what kind of data source you are using, and / or how that report data is generated. If there's a database behind that "report", then a query against that database, that produces that report, might be more helpful than using the report itself. However, if that's not possible, then I'd go with Phil's suggestion to use a Script Component to modify the rows "in flight", so to speak, using a variable within the script to hold on to the previous value from a previous row.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks for the feedback guys
I have tried implementing a script component to transform the data but am g now getting the following error messages
The collection of variables locked for read and write access is not available outside of PostExecute.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()
at Variables.set_TempestRefVariable(String value)
at ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at UserComponent.ProcessInput(Int32 InputID, String InputName, PipelineBuffer Buffer, OutputNameMap OutputMap)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.ProcessInput(Int32 InputID, PipelineBuffer buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
This is the code I have used in the script component
public override void PreExecute()
{
base.PreExecute();
}public override void PostExecute()
{
base.PostExecute();
Variables.TempestRefVariable = "Stat";
}public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.TempestRef_IsNull)
{
Row.TempestRef = Variables.TempestRefVariable.ToString();
}
else
{
Variables.TempestRefVariable = Row.TempestRef;
}
} -
Try this (untested and may require refinement)
private string tempestRef;
public override void PostExecute()
{
base.PostExecute();
}public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.TempestRef_IsNull)
{
Row.TempestRef = tempestRef;
}
else
{
tempestRef = Row.TempestRef;
}
}The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Andrew.Murdoch - Monday, November 6, 2017 9:42 AM
You'll need to go into the properties of the Script Component and add that package variable to the "Read/Write" list.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Monday, November 6, 2017 11:52 AM
Locking and unlocking package variables inside a ProcessInputRow method is not recommended, as the operation has to be performed for every row of data passing through the script component. In fact, adding the variable to the read/write list would probably not help here, as the error message suggests.
It's less overhead (and simpler) to use a private local variable in this case.The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Monday, November 6, 2017 12:14 PM
True enough, but that also means that the variable will have to be declared in the PreExecute portion of the code.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thanks for the help guys
Got it working using the script component and Phil's suggestion of using a local variable
- sgmunson - Monday, November 6, 2017 2:33 PM
While assignment can happen in the PreExecute section, declaration needs to happen at the class level, like this:
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Tuesday, November 7, 2017 6:35 AMsgmunson - Monday, November 6, 2017 2:33 PM
While assignment can happen in the PreExecute section, declaration needs to happen at the class level, like this:
Thanks for that update... I'm not real good with C#, although I at least did know it would have to get declared somewhere outside of that routine...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I would still prefer to connect to the source database and run the select statement behind the report rather than consume the report as a data source.
- aaron.reese - Wednesday, November 8, 2017 4:09 AM
As would I... but you know how real life loves to throw curve-balls...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, November 8, 2017 6:43 AM
That's where bats, hammers, and high velocity pork chops become incredibly useful.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply