Showing posts with label SSIS SQL Script file replace database name. Show all posts
Showing posts with label SSIS SQL Script file replace database name. Show all posts

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");