January 16, 2017 at 9:47 am
I have a package which uses a csv as input and I need to populate several columns (12) with data from the previous row when that data is missing. As I am inexperienced with SSIS and know nothing about C#, I figured it would be best to start with one column and apply that solution to the other columns. Very simply, when the column MRN is null, use the PrimaryInsuranceCompanyID from the previous row to populate PrimaryInsuranceCompnayIDout. Unfortunately, the script I have tried does not seem to be working as I expected it to as PrimaryInsuranceCompnayIDout has 0 in each row of the output flat file.
Is it possible to simply replace PrimaryInsuranceCompanyId? I received a message that it was Read-Only when I tried to do that.
/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string primins = "";
string PrimaryInsuranceCompanyIDout;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.MRN == "")
{
PrimaryInsuranceCompanyIDout = primins;
}
else
{
PrimaryInsuranceCompanyIDout = Row.PrimaryInsuranceCompanyID;
primins = Row.PrimaryInsuranceCompanyID;
}
}
}
My thoughts are/were, if MRN on the input file is NULL, then I want to put the contents of primins into PrimaryInsuranceCompanyIDout. If MRN has data in it then I want to set both primins and PrimaryInsurancecompanyIDout to the value of PrimaryInsuranceID. Thanks for any help.
January 16, 2017 at 10:38 am
alicesql - Monday, January 16, 2017 9:47 AMI have a package which uses a csv as input and I need to populate several columns (12) with data from the previous row when that data is missing. As I am inexperienced with SSIS and know nothing about C#, I figured it would be best to start with one column and apply that solution to the other columns. Very simply, when the column MRN is null, use the PrimaryInsuranceCompanyID from the previous row to populate PrimaryInsuranceCompnayIDout. Unfortunately, the script I have tried does not seem to be working as I expected it to as PrimaryInsuranceCompnayIDout has 0 in each row of the output flat file.
Is it possible to simply replace PrimaryInsuranceCompanyId? I received a message that it was Read-Only when I tried to do that./* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string primins = "";
string PrimaryInsuranceCompanyIDout;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.MRN == "")
{
PrimaryInsuranceCompanyIDout = primins;
}
else
{
PrimaryInsuranceCompanyIDout = Row.PrimaryInsuranceCompanyID;
primins = Row.PrimaryInsuranceCompanyID;
}
}
}
My thoughts are/were, if MRN on the input file is NULL, then I want to put the contents of primins into PrimaryInsuranceCompanyIDout. If MRN has data in it then I want to set both primins and PrimaryInsurancecompanyIDout to the value of PrimaryInsuranceID. Thanks for any help.
I just had a look at this in VS2015, which I think is a few versions ahead of yours, so my findings may not match what you see. But, when you open the Script Component's property box, you should see a node on the left called 'Input Columns'. Make sure that PrimaryInsuranceCompanyID appears here, with a usage type of 'ReadWrite'.
Once that's in place, you should be able to write to it directly:
Row.PrimaryInsuranceCompanyID = primins; etc
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
January 18, 2017 at 7:14 am
Phil Parkin - Monday, January 16, 2017 10:38 AMalicesql - Monday, January 16, 2017 9:47 AMI have a package which uses a csv as input and I need to populate several columns (12) with data from the previous row when that data is missing. As I am inexperienced with SSIS and know nothing about C#, I figured it would be best to start with one column and apply that solution to the other columns. Very simply, when the column MRN is null, use the PrimaryInsuranceCompanyID from the previous row to populate PrimaryInsuranceCompnayIDout. Unfortunately, the script I have tried does not seem to be working as I expected it to as PrimaryInsuranceCompnayIDout has 0 in each row of the output flat file.
Is it possible to simply replace PrimaryInsuranceCompanyId? I received a message that it was Read-Only when I tried to do that./* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string primins = "";
string PrimaryInsuranceCompanyIDout;
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.MRN == "")
{
PrimaryInsuranceCompanyIDout = primins;
}
else
{
PrimaryInsuranceCompanyIDout = Row.PrimaryInsuranceCompanyID;
primins = Row.PrimaryInsuranceCompanyID;
}
}
}
My thoughts are/were, if MRN on the input file is NULL, then I want to put the contents of primins into PrimaryInsuranceCompanyIDout. If MRN has data in it then I want to set both primins and PrimaryInsurancecompanyIDout to the value of PrimaryInsuranceID. Thanks for any help.I just had a look at this in VS2015, which I think is a few versions ahead of yours, so my findings may not match what you see. But, when you open the Script Component's property box, you should see a node on the left called 'Input Columns'. Make sure that PrimaryInsuranceCompanyID appears here, with a usage type of 'ReadWrite'.
Once that's in place, you should be able to write to it directly:
Row.PrimaryInsuranceCompanyID = primins; etc
Thanks! I no longer receive an error message, which is good! But my condition never seems to be true. I changed the IF statement to if (Row.MRN == " " || Row.MRN_IsNull) but neither condition is ever satisfied. How can I tell what value may be in that column? It isn't a space and it isn't null? The file source is a csv...
January 18, 2017 at 7:25 am
alicesql - Wednesday, January 18, 2017 7:14 AMThanks! I no longer receive an error message, which is good! But my condition never seems to be true. I changed the IF statement toif (Row.MRN == " " || Row.MRN_IsNull)but neither condition is ever satisfied. How can I tell what value may be in that column? It isn't a space and it isn't null? The file source is a csv...
A space is unlikely. Try A space is unlikely. Try If (string.IsNullOrEmpty(Row.MRN)) ... ...
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
January 18, 2017 at 7:28 am
Phil Parkin - Wednesday, January 18, 2017 7:25 AMalicesql - Wednesday, January 18, 2017 7:14 AMThanks! I no longer receive an error message, which is good! But my condition never seems to be true. I changed the IF statement toif (Row.MRN == " " || Row.MRN_IsNull)but neither condition is ever satisfied. How can I tell what value may be in that column? It isn't a space and it isn't null? The file source is a csv...A space is unlikely. Try
A space is unlikely. Try If (string.IsNullOrEmpty(Row.MRN)) ......
Yay! That did it thank you very much for your time and help!
January 18, 2017 at 7:32 am
alicesql - Wednesday, January 18, 2017 7:28 AMPhil Parkin - Wednesday, January 18, 2017 7:25 AMalicesql - Wednesday, January 18, 2017 7:14 AMThanks! I no longer receive an error message, which is good! But my condition never seems to be true. I changed the IF statement toif (Row.MRN == " " || Row.MRN_IsNull)but neither condition is ever satisfied. How can I tell what value may be in that column? It isn't a space and it isn't null? The file source is a csv...A space is unlikely. Try
A space is unlikely. Try If (string.IsNullOrEmpty(Row.MRN)) ......Yay! That did it thank you very much for your time and help!
Great, thanks for posting back.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply