Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SSIS - variable for specific character within the data?

Posted on 2012-03-22
17
Medium Priority
?
724 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

604 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