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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
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 :)).
simplyfemalesAuthor Commented:
I had a similar suggestion from someone outside EE.  My scripting experience is extremely limited.

Any links to suggested examples perhaps?
ValentinoVBI ConsultantCommented:
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;
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

simplyfemalesAuthor Commented:
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?
ValentinoVBI ConsultantCommented:
" I assume I have to create two variables?  InputFile and OutputFile? "

You do indeed need to create two package variables: InputFile and OutputFile.

"How do I have the script evaluate each file?"

InputFile is given a value in the ForEach loop, through the Variable Mappings page in the ForEach Loop Editor (screenshot number 6 in article through first link).

"How do I get that character and then tell the script to replace all those identical characters found within the file?"

The first line of code loads the file into a variable called fileContent.  You can then use the Substring function to find that char at position 104.  Something like this:

public void Main()
  string fileContent = System.IO.File.ReadAllText(Dts.Variables["InputFile"].Value.ToString());
  string delimiterInfile = fileContent.Substring(103, 1); //103 because it's zero-based
  fileContent = fileContent.Replace(delimiterInfile, ';');
  System.IO.File.WriteAllText(Dts.Variables["OutputFile"].Value.ToString(), fileContent);
  Dts.TaskResult = (int)ScriptResults.Success;

Note: this assumes you'd like the new delimiter to be semi-colon.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
simplyfemalesAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
Try building the script after you modify it and before you close the designer.
simplyfemalesAuthor Commented:
The best overloaded method match for 'string.Replace(string, string)' has some invalid arguments

Argument '2'" cannot convert from 'char' to 'string'
simplyfemalesAuthor Commented:
BTW, yes I did Build.  Hadn't done that previously.  The errors above are the result of the build.
simplyfemalesAuthor Commented:
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?
simplyfemalesAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
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?
simplyfemalesAuthor Commented:
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
simplyfemalesAuthor Commented:
I don't have any idea why, but it finally worked.

I changed:

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


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!!!

simplyfemalesAuthor Commented:
Appeared to possibly be some very small syntax problem, but otherwise the suggestion was solid.
ValentinoVBI ConsultantCommented:
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?
simplyfemalesAuthor Commented:
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

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.