Solved

SSIS - variable for specific character within the data?

Posted on 2012-03-22
17
706 Views
Last Modified: 2012-03-28
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?
0
Comment
Question by:simplyfemales
  • 11
  • 6
17 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37756432
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 :)).
0
 

Author Comment

by:simplyfemales
ID: 37757451
I had a similar suggestion from someone outside EE.  My scripting experience is extremely limited.

Any links to suggested examples perhaps?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37764692
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;
}
0
 

Author Comment

by:simplyfemales
ID: 37767277
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?
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37770873
" 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.
0
 

Author Comment

by:simplyfemales
ID: 37772682
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.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37772693
Try building the script after you modify it and before you close the designer.
0
 

Author Comment

by:simplyfemales
ID: 37772733
The best overloaded method match for 'string.Replace(string, string)' has some invalid arguments

Argument '2'" cannot convert from 'char' to 'string'
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:simplyfemales
ID: 37772736
BTW, yes I did Build.  Hadn't done that previously.  The errors above are the result of the build.
0
 

Author Comment

by:simplyfemales
ID: 37772860
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?
0
 

Author Comment

by:simplyfemales
ID: 37775211
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.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37775257
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?
0
 

Author Comment

by:simplyfemales
ID: 37779495
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
0
 

Author Comment

by:simplyfemales
ID: 37779550
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!
0
 

Author Closing Comment

by:simplyfemales
ID: 37779555
Appeared to possibly be some very small syntax problem, but otherwise the suggestion was solid.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37780378
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?
0
 

Author Comment

by:simplyfemales
ID: 37780385
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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now