February 6, 2017 at 5:53 pm
I want to check if current database is EMP then i have to define var at package level set default value there -eg: a query to set select * from emptemp.otherwise else if current database is STUD then need to set another var ( select * from studetemp)
In the above code i want to check this condition before Dts.TaskResult = (int)ScriptResults.Success;
in DFT task at package - how to use the varaiable in data flow task to load data from var - set default query to another target table.
public void Main()
{
string EncStr = Dts.Variables["EcryptedConnString"].Value.ToString();
string DecStr = EncryptionFactory.GetCipherObject(CipherMethod.Decrypt, EncStr .ToString());
Dts.Variables["DcryptedConnString"].Value = DecStr;
ConnectionManager connMgr = Dts.Connections["EMP"];
connMgr.ConnectionString = DecStr;
if (!GetConnectionProperty(connMgr, "InitialCatalog"))
GetConnectionProperty(connMgr, "Database");
Dts.TaskResult = (int)ScriptResults.Success;
}
private bool GetConnectionProperty(ConnectionManager connMgr, string propName)
{
DtsProperties properties = connMgr.Properties;
if (properties.Contains(propName))
{
DtsProperty property = properties[propName];
Dts.Variables["User::DatabaseSource"].Value = property.GetValue(connMgr).ToString();
return true;
}
return false;
}
February 6, 2017 at 9:54 pm
In SSIS package,
1. Store the SQL query in the variable, define it using expression
2. Connection Managers contains InitialCatalog property, if it is set dynamically (i.e., using expression) then use the same expression in setting up the table name in the SQL query.
This will be easier than utilizing the ScriptTask.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply