Link to home
Start Free TrialLog in
Avatar of simplyfemales
simplyfemales

asked on

SSIS - variable for specific character within the data?

I need to evaluate a collection of files based on a specific character in the data.  Specifically, it will be located in row 1, character # 104.

When that character is shown, it identifies the delimiter for the file and then I would like to create a task to replace all of the first characters in the file with the second (so that I don't have to create multiple workflows, rather I can have one workflow and just adjust the incoming files to work with it).

I was thinking on using a script task with a precedence constraint that would have an expression that utilizes a variable based on the "found" delimiter character.

Any suggestions?
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Here's what I would probably do.

Create a ForEach loop over all files.  In the loop use a Script task to read the file into a variable, search for the delimiter, replace all occurences of the delimiter with the one you'll use to create the connection manager and write the variable to a new file (in a different folder).

Now you've got all files all with the same delimiter in the other folder so you can create a second loop with a Data Flow task to import them.

The above assumes that you can simply find/replace the delimiter over the whole content of the file.  It may or may not work, depending on your situation.

If the above doesn't already help you further, can you post a simple example of your file content?  Just some lines, and make sure nothing sensitive is in there (we don't want to see your manager's salary here :)).
Avatar of simplyfemales
simplyfemales

ASKER

I had a similar suggestion from someone outside EE.  My scripting experience is extremely limited.

Any links to suggested examples perhaps?
Looping over files in SSIS
ForEach Loop on MSDN

Some specific help with the Script task: the following code sample (C#) takes an input package variable InputFile (which contains path + file name, e.g. "d:\TheFolder\AFile.txt"), reads the content of the file into a string variable, replaces all commas with semicolons and writes it back to a file.  Location of the output file is specified through another package variable called OutputFile.  Both InputFile and OutputFile will need to be specified in the ReadOnlyVariables of the Script task.

public void Main()
{
  string fileContent = System.IO.File.ReadAllText(Dts.Variables["InputFile"].Value.ToString());
  fileContent = fileContent.Replace(',', ';');
  System.IO.File.WriteAllText(Dts.Variables["OutputFile"].Value.ToString(), fileContent);
  Dts.TaskResult = (int)ScriptResults.Success;
}
I'm familiar with the looping links you provided, but thank you for those.  Great reminders

However.  I have created the script task and input the script you included.  I assume I have to create two variables?  InputFile and OutputFile?  Or I have to do something beyond that?

The files are looped over in the ForEach container.  How do I have the script evaluate each file?

Second.  The find and replace has to be done based on each file.  Character#104 of line 1 will need to be replaced with a standard character.  The character in #104 is a delimiter which I need to change in the whole file.  Therefore, it's unknown until the file is read.  How do I get that character and then tell the script to replace all those identical characters found within the file?
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Validation error. Script task : The binary code for the script is not found.  Please open the script in the designer by clicking Edit Script button and make sure it builds successfully.

fileContent = fileContent.Replace(delimiterInfile, ';');

when I remove that line, the error goes away.
Try building the script after you modify it and before you close the designer.
The best overloaded method match for 'string.Replace(string, string)' has some invalid arguments

Argument '2'" cannot convert from 'char' to 'string'
BTW, yes I did Build.  Hadn't done that previously.  The errors above are the result of the build.
it looks like maybe that line needs to be renamed?  perhaps:

fileContent2 = filecontent.Replace(delimiterInfile, ';');

I think it looks like the line is referencing itself rather than creating something new from something old?  Does that sound right?
Nope, the last one didn't do it.  Not sure how to approach this now.  The fileContent = fileContent.Replace(delimiterInfile, ';'); is throwing it off somehow.
I don't think it's a code problem but for some reason your script task doesn't seem to recompile the newest version of the code.
Could you try to delete the task and create a new one?  That should normally solve the problem.

BTW: do you have the latest service packs for both SQL Server and Visual Studio installed?
task deleted, new task created.  Same problem.

All updates are current.

Using WIN Server 2003, SQL Server 2008 R2, Visual Studio 2008

Still having the same errors
I don't have any idea why, but it finally worked.

I changed:


fileContent = fileContent.Replace(delimiterInfile, ';');


to


fileContent = fileContent.Replace("*", ";");

did a build with success

then changed to

fileContent = fileContent.Replace(delimiterInfile, ";");

and did a build with success
.....

You may notice that I changed the single quotes to double quotes.  I'm not skilled enough to know the difference on the two as of yet for C#, but when I did Google searches, I found all the string.Replace suggestions using double quotes and thought I'd try it out.

Thanks for the script.  Now I get to test it and see if it functions for my files!!!

Thanks!
Appeared to possibly be some very small syntax problem, but otherwise the suggestion was solid.
Wow, when I last replied I had missed a couple of your replies somehow, including the one with the syntax error message!  Good that you manager to solve it though!

Here's some additional info:

"The best overloaded method match for 'string.Replace(string, string)' has some invalid arguments

Argument '2'" cannot convert from 'char' to 'string'"


That's right, I accidentally chose the wrong type of quotes.  In C# a hard-coded string should be surrounded by double quotes, while the single quotes surround a character, or char.  The Replace method has not been implemented to accept one string and one char parameter, thus the error.

"fileContent = filecontent.Replace(delimiterInfile, ';');

I think it looks like the line is referencing itself rather than creating something new from something old?  Does that sound right?"


The fileContent variable represents a string object.  The existing object is being used to execute the Replace method.  The result of that method is assigned to the variable, thereby replacing the existing object with the new one created by the Replace call.

Hopefully that clarifies things a little?
Yes, the information helps.  Explains better what I worked through.

In particular, it appears that the code/scripting is linear and logical so it works it's way through it line by line..... meaning that information in a specific item can be replaced and doesn't require renaming.

You may be interested in looking at http://rdsrc.us/1rLVUK......

This is a link to my new question.  I'm getting a error indicating that there's a problem with an empty path name is not legal.......

I know the ForEach file collection works as it's been used in a disabled DataFlow task.  Not sure what to do with it now though.  If you have any suggestions, I'd appreciate it.  The full code and the error are contained in that link.