SSIS Script question

Question

SSIS Script question

isuckatsql

SSChampion

Points: 10381
More actions
October 19, 2011 at 11:18 am

#245622

I have the following script, thich works well when i use a folder with text documents.
I have changed the input to be a DB table and it is no longer working.
What do i need to change in the script for it to read from a SQL DB instead of a folder?
Thanks
/* 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;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Collections;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}
public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
ArrayList mailList = ValidateEmailRow(System.Text.Encoding.Unicode.GetString(Row.DocContent.GetBlobData(0,(int)(Row.DocContent.Length-1))));
DateTime dt;
dt=DateTime.Now;
Row.currentdate = dt;
int i = mailList.Count;
if(i>0)
{
Row.email1 = (i>0)?mailList[0].ToString():"";
Row.email2 = (i>1)?mailList[1].ToString():"";
Row.email3 = (i>2)?mailList[2].ToString():"";
Row.email4 = (i>3)?mailList[3].ToString():"";
Row.email5 = (i>4)?mailList[4].ToString():"";
Row.email6 = (i>5)?mailList[5].ToString():"";
Row.email7 = (i>6)?mailList[6].ToString():"";
Row.email8 = (i>7)?mailList[7].ToString():"";
Row.email9 = (i>8)?mailList[8].ToString():"";
Row.email10 = (i>9)?mailList[9].ToString():"";
}
mailList = null;
}
public ArrayList ValidateEmailRow(string email)
{
ArrayList emailList = new ArrayList();
//Regex reg = new Regex(@"\b([a-zA-Z0-9._%+-]+)@([a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})\b",RegexOptions.IgnoreCase);
Regex reg = new Regex(@"(?<=Total years experience.*?)\b\S{1,}\b(?=.*?Job Categories)");
Match validate = reg.Match(email);
while (validate.Success)
{
if (emailList.LastIndexOf(validate.Value) < 0)
{
emailList.Add(validate.Value.ToLower().Trim());
}
validate = validate.NextMatch();
}
return emailList;
}
}

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I don't see you accessing a data source in this script. Are you getting an error?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

steveb. SSC-Forever Points: 46733 More actions · Answer 2

have you changed the data source that passes the data flow into this script?

isuckatsql SSChampion Points: 10381 More actions · Answer 3

yes, the data source is now the input SQL DB table.

The SSIS project says it writes one record, while testing, but nothing is written, as far as i can tell!

What is weird is that if i use the desktop folder as my input, it works perfectly.

I dont have much experience in scripting, so i thought i may be missing a class reference or something?

Thanks

steveb. SSC-Forever Points: 46733 More actions · Answer 4

I would check that the datatypes are the same as from the flat file, esp the BLOB data.

The script should throw an error if there is a problem in there.

you could try adding some breakpoint to the connector before and after the script to see exactly what is being passed in and out of the script.

isuckatsql SSChampion Points: 10381 More actions · Answer 5

The original input field was 'nvarchar(max)' and now the input field is 'text'.

The SSIS input field format is 'DT_TEXT', which i have not changed. Should it be something different for a text input vs an nvarchar(max) input?

In preview the data displays fine, and it also displays in the grid view, before and after the script.

Thanks

R_S Ten Centuries Points: 1160 More actions · Answer 6

Can you explain what you are trying to do here? Where are you using the data source (Which variable or connection manager)

isuckatsql SSChampion Points: 10381 More actions · Answer 7

//ArrayList mailList = ValidateEmailRow(System.Text.Encoding.Unicode.GetString(Row.DocContent.GetBlobData(0,(int)(Row.DocContent.Length-1))));

ArrayList mailList = ValidateEmailRow(System.Text.Encoding.Default.GetString(Row.DocContent.GetBlobData(0, (int)(Row.DocContent.Length - 1))));

Changed Unicode to Default in the above line and it is working 🙂

isuckatsql SSChampion Points: 10381 More actions · Answer 8

Error1Error loading email integer works on experience JobPortalIANDB-Load of CB resumes 10-19-11 2.dtsx: The connection "{F07CF98B-1C21-4A2E-B7B2-D539D05F1AD0}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I am getting the above error, and i'm not really sure what it means.

The email field type was varchar, but i changed it integer, to match the receiving field type.

steveb. SSC-Forever Points: 46733 More actions · Answer 9

that error usually occurs when you renamed a connection manager and not updated the compoenet that refered to it.

find which component is referencing it and click on it and change the connection manger to the correct one.