August 3, 2017 at 2:12 pm
Hi Guys,
Thank you all for your help. I successfully achieved a solution to this problem using Script component.. I had to just replicate same logic as power shell script. it was pretty easy in Script Component. i am getting the desired output now.
Regards,
Shweta
August 4, 2017 at 10:46 am
Are you looking for something along the lines of excel's 'copy down'?
This script (SSIS) will copy values of several fields when one field is empty/blank. If could be modified to suit your purposes. I'm not a C expert, so I'm hesitant to recommend this, but it does work for me and perhaps there are more experienced C folks that can elaborate on it.
/* 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 PrevMRN = " ";
string PrevFIN = " ";
string PrevFN = " ";
string PrevMN = " ";
string PrevLN = " ";
string PrevDOB = " ";
string PrevGender = " ";
string PrevPtCls = " ";
string Prev1InsCd = " ";
string Prev1InsNm = " ";
string Prev2InsCd = "none";
string Prev2InsNm = "none";
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if ((string.IsNullOrEmpty(Row.MRN)) )
{
Row.MRN = PrevMRN;
Row.FIN = PrevFIN;
Row.LastName = PrevLN;
Row.FirstName = PrevFN;
Row.MiddleName = PrevMN;
Row.DOB = PrevDOB;
Row.Gender = PrevGender;
Row.PatientClass = PrevPtCls;
Row.PrimaryInsuranceCompanyID = Prev1InsCd;
Row.PrimaryInsuranceCompanyName = Prev1InsNm;
Row.SecondaryInsuranceCompanyID = Prev2InsCd;
Row.SecondaryInsuranceCompanyName = Prev2InsNm;
}
else
{
PrevMRN = Row.MRN;
PrevFIN = Row.FIN;
PrevLN = Row.LastName;
PrevMN = Row.MiddleName;
PrevFN = Row.FirstName;
PrevDOB = Row.DOB;
PrevGender = Row.Gender;
PrevPtCls = Row.PatientClass;
Prev1InsCd = Row.PrimaryInsuranceCompanyID;
Prev1InsNm = Row.PrimaryInsuranceCompanyName;
Prev2InsCd = Row.SecondaryInsuranceCompanyID;
Prev2InsNm = Row.SecondaryInsuranceCompanyName;
}
}
}
August 4, 2017 at 10:50 am
alicesql - Friday, August 4, 2017 10:46 AMAre you looking for something along the lines of excel's 'copy down'?This script (SSIS) will copy values of several fields when one field is empty/blank. If could be modified to suit your purposes. I'm not a C expert, so I'm hesitant to recommend this, but it does work for me and perhaps there are more experienced C folks that can elaborate on it.
/* 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 PrevMRN = " ";
string PrevFIN = " ";
string PrevFN = " ";
string PrevMN = " ";
string PrevLN = " ";
string PrevDOB = " ";
string PrevGender = " ";
string PrevPtCls = " ";
string Prev1InsCd = " ";
string Prev1InsNm = " ";
string Prev2InsCd = "none";
string Prev2InsNm = "none";public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if ((string.IsNullOrEmpty(Row.MRN)) )
{
Row.MRN = PrevMRN;
Row.FIN = PrevFIN;
Row.LastName = PrevLN;
Row.FirstName = PrevFN;
Row.MiddleName = PrevMN;
Row.DOB = PrevDOB;
Row.Gender = PrevGender;
Row.PatientClass = PrevPtCls;
Row.PrimaryInsuranceCompanyID = Prev1InsCd;
Row.PrimaryInsuranceCompanyName = Prev1InsNm;
Row.SecondaryInsuranceCompanyID = Prev2InsCd;
Row.SecondaryInsuranceCompanyName = Prev2InsNm;}
else
{
PrevMRN = Row.MRN;
PrevFIN = Row.FIN;
PrevLN = Row.LastName;
PrevMN = Row.MiddleName;
PrevFN = Row.FirstName;
PrevDOB = Row.DOB;
PrevGender = Row.Gender;
PrevPtCls = Row.PatientClass;
Prev1InsCd = Row.PrimaryInsuranceCompanyID;
Prev1InsNm = Row.PrimaryInsuranceCompanyName;
Prev2InsCd = Row.SecondaryInsuranceCompanyID;
Prev2InsNm = Row.SecondaryInsuranceCompanyName;
}
}
}
Thanks Alice for the code. but that was not i am looking for exactly. Nevertheless i got the solution. i wrote something similar in script component. Thanks again! Appreciate your response 🙂
Regards,
Shweta
August 4, 2017 at 1:29 pm
alicesql - Friday, August 4, 2017 10:46 AMAre you looking for something along the lines of excel's 'copy down'?This script (SSIS) will copy values of several fields when one field is empty/blank. If could be modified to suit your purposes. I'm not a C expert, so I'm hesitant to recommend this, but it does work for me and perhaps there are more experienced C folks that can elaborate on it.
Just a quick note: this language is C# rather than C. Despite the similar names, the languages are quite different.
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 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply