Thursday 17 November 2011

How to replace database name in 100 sql script files

Hi I have 100 sql files.. in that I need replace database name xyz to abc..
Some the table name specified as xyz.<tablename> to abc.<tablename> in all the files and full content using ssis?

Approach:
1)use foreach loop and enumerator file...

and expressions specify as *.sql and give folder name where sql files exist.

in for each loop assign a varible to result to get file name

use script task in foreach loop

then

public
{


void Main()String ErrInfo = "";String FilePath = Dts.Variables["User::filename"].Value.ToString();try{
String FileContent; //Variable to store File ContentsFileContent = ReadFile(FilePath, ErrInfo);

{
Dts.Log(
Dts.Log(ErrInfo, 0,
Dts.TaskResult = (

}

WriteToFile(FilePath, FileContent, ErrInfo);if (ErrInfo.Length > 0)"Error while writing File " + FilePath, 0, null);null);int)ScriptResults.Failure;return;catch (Exception e)null);int)ScriptResults.Failure;public String ReadFile(String FilePath, String ErrInfo)String strContents;StreamReader sReader;try{
sReader =
strContents = sReader.ReadToEnd();
sReader.Close();

}

{

ErrInfo = e.Message;

}
}

{

File.OpenText(FilePath);return strContents;catch (Exception e)MessageBox.Show(ErrInfo);return "";public void WriteToFile(String FilePath, String strContents, String ErrInfo)StreamWriter sWriter;try{
sWriter =
sWriter.Write(strContents);
sWriter.Close();
}

{

ErrInfo = e.Message;
}
}
new StreamWriter(FilePath);catch (Exception e)MessageBox.Show(ErrInfo);

{
Dts.Log(
Dts.Log(ErrInfo, 0,
Dts.TaskResult = (

}
}

{
Dts.Log(e.Message, 0,
Dts.TaskResult = (
}
}

{


if (ErrInfo.Length > 0)"Error while reading File " + FilePath, 0, null);null);int)ScriptResults.Failure;return;FileContent = FileContent.Replace("LAjit", "Feature1BLajit");

No comments:

Post a Comment