/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using RunTimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Pipeline;
using Comp3 = CustomComponents.UnpackDecimalComponent;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;
namespace ST_394d7e0c32ff40af81e953483ef3c2cf.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
Package _pgk = new Package();
Application _app = new Application();
String SavePath = "";
public void Main()
{
Variables vars = null;
Dts.VariableDispenser.LockForRead("User::strTabListCmd");
Dts.VariableDispenser.LockForRead("User::strSqlCmdFilter");
Dts.VariableDispenser.LockForRead("User::strColumnCmd");
Dts.VariableDispenser.LockForRead("User::strSavePath");
Dts.VariableDispenser.LockForRead("User::strFileDirectory");
Dts.VariableDispenser.GetVariables(ref vars);
try
{
String CmdTabList = vars["User::strTabListCmd"].Value.ToString();
String CmdFilter = vars["User::strSqlCmdFilter"].Value.ToString();
String CmdColumns = vars["User::strColumnCmd"].Value.ToString();
String FileDirectory = vars["User::strFileDirectory"].Value.ToString();
SavePath = vars["User::strSavePath"].Value.ToString();
String ServerName = @"JNOVO";
String DatabaseName = @"B";
String ConnectionString = "Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True;";
DataTable _DataTable = ExecuteCommand(ConnectionString, CmdTabList);
int CntRows = _DataTable.Rows.Count;
foreach (DataRow row in _DataTable.Rows)
{
String FileName = (String)row["FileName"];
String TabName = (String)row["TabName"];
String Filter = CmdFilter;
String Fiter2 = Filter.Replace("<<Filter>>", TabName);
StringBuilder NewCmd = new StringBuilder();
PipeLineWrapper.IDTSPath100 SourceToDecimal;
NewCmd.Append(CmdColumns);
NewCmd.Append(Fiter2);
DataTable connColumns = ExecuteCommand(ConnectionString, NewCmd.ToString());
ConnectionManager _ConnMgr = AddFixedWith(connColumns, TabName, FileDirectory + "\\" + FileName + ".DAT");
PipeLineWrapper.MainPipe dataflow = AddWorkFlowComponent(TabName);
PipeLineWrapper.IDTSComponentMetaData100 FileSource = AddFileSourceComponent(dataflow, _ConnMgr, TabName);
PipeLineWrapper.IDTSComponentMetaData100 UnpackDec = AddUnpackDecimal(dataflow);
SourceToDecimal = dataflow.PathCollection.New();
SourceToDecimal.AttachPathAndPropagateNotifications(FileSource.OutputCollection[0], UnpackDec.InputCollection[0]);
SetFlatFileProperty(FileSource);
MappDecimalColumns(UnpackDec);
// MappDecimals(UnpackDec);
connColumns = null;
NewCmd = null;
Filter = null;
TabName = null;
Fiter2 = null;
}
_app.SaveToXml(SavePath + "test04.dtsx", _pgk, null);
//Dispose of table
// TODO: Add your code here
vars.Unlock();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception e)
{
vars.Unlock();
Console.WriteLine(e.ToString());
_app.SaveToXml(SavePath + "test04.dtsx", _pgk, null);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
private void MappDecimalColumns(PipeLineWrapper.IDTSComponentMetaData100 PackedDecimal)
{
PipeLineWrapper.IDTSInputCollection100 inputCollection = PackedDecimal.InputCollection;
PipeLineWrapper.IDTSInput100 input = inputCollection[0];
PipeLineWrapper.IDTSVirtualInput100 vinput = input.GetVirtualInput();
PipeLineWrapper.IDTSVirtualInputColumnCollection100 vInputColumns = vinput.VirtualInputColumnCollection;
PipeLineWrapper.CManagedComponentWrapper InstanceUnpack = PackedDecimal.Instantiate();
//InstanceUnpack.ProvideComponentProperties();
//InstanceUnpack.ReleaseConnections();
foreach (PipeLineWrapper.IDTSVirtualInputColumn100 virtualInputColumn in vInputColumns)
{
if (virtualInputColumn.DataType == RunTimeWrapper.DataType.DT_BYTES)
{
// Select column, and retain new input column
PipeLineWrapper.IDTSInputColumn100 inputColumn = InstanceUnpack.SetUsageType(input.ID, vinput, virtualInputColumn.LineageID, PipeLineWrapper.DTSUsageType.UT_READONLY);
inputColumn.CustomPropertyCollection["PackedScale"].Value = 2;
PipeLineWrapper.IDTSOutputCollection100 outputCollection = PackedDecimal.OutputCollection;
PipeLineWrapper.IDTSOutput100 output = outputCollection[0];
PipeLineWrapper.IDTSOutputColumnCollection100 outputcolumncollection = output.OutputColumnCollection;
PipeLineWrapper.IDTSOutputColumn100 outcol = output.OutputColumnCollection[inputColumn.Name];
}
}
}
private void SetFlatFileProperty(PipeLineWrapper.IDTSComponentMetaData100 FileSource)
{
PipeLineWrapper.IDTSOutputCollection100 ocols1 = FileSource.OutputCollection;
PipeLineWrapper.IDTSOutput100 ocols = ocols1[0];
foreach (PipeLineWrapper.IDTSOutputColumn100 col in ocols.OutputColumnCollection)
{
RunTimeWrapper.DataType dty = col.DataType;
if (dty == RunTimeWrapper.DataType.DT_BYTES)
{
col.CustomPropertyCollection["UseBinaryFormat"].Value = true;
//PipeLineWrapper.IDTSInput100 decInput = inputCollection.New();
//PipeLineWrapper.IDTSInputColumn100 inputCol = decInput.InputColumnCollection.New();
//inputCol.Name = col.Name;
//inputCol.ExternalMetadataColumnID = col.ExternalMetadataColumnID;
//inputCol.LineageID = col.LineageID;
//inputCol.CustomPropertyCollection["PackedScale"].Value = "0";
}
}
}
private PipeLineWrapper.IDTSComponentMetaData100 AddUnpackDecimal(PipeLineWrapper.MainPipe dataFlowTask)
{
//Comp3 unpk = new Comp3();
//PipeLineWrapper.IDTSComponentMetaData100 mpk = unpk.ComponentMetaData;
PipeLineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();
mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
//mpk.ComponentClassID = typeof(Comp3).AssemblyQualifiedName;
mpk.Name = "UnpackDecimalTest";
PipeLineWrapper.CManagedComponentWrapper instanceFileSource = mpk.Instantiate();
instanceFileSource.ProvideComponentProperties();
return mpk;
}
private void Reinitiaze(PipeLineWrapper.CManagedComponentWrapper InstanceSource)
{
//Reinitialize Flat File source metadata,
InstanceSource.AcquireConnections(null);
InstanceSource.ReinitializeMetaData();
InstanceSource.ReleaseConnections();
}
private DataTable ExecuteCommand(String ConnectionString, String qrty)
{
SqlConnection BDEConnection = new SqlConnection(ConnectionString);
DataTable rtData = new DataTable();
BDEConnection.Open();
SqlCommand SqlCmd = new SqlCommand(qrty,BDEConnection);
SqlDataReader DtReader = SqlCmd.ExecuteReader();
rtData.Load(DtReader);
BDEConnection.Close();
return rtData;
}
private ConnectionManager AddFixedWith(DataTable dtColumns, String ConnectionName, String ConnectionString)
{
ConnectionManager flatConn = _pgk.Connections.Add("FLATFILE");
flatConn.Name = ConnectionName;
flatConn.ConnectionString = ConnectionString;
flatConn.Properties["Format"].SetValue(flatConn, "FixedWidth");
flatConn.Properties["CodePage"].SetValue(flatConn, "37");
flatConn.Properties["DataRowsToSkip"].SetValue(flatConn, 1);
flatConn.Properties["RowDelimiter"].SetValue(flatConn, "\r\n");
flatConn.Properties["LocaleID"].SetValue(flatConn, "1033");
//Inner object
RunTimeWrapper.IDTSConnectionManagerFlatFile100 ConnectionFlatFile = (RunTimeWrapper.IDTSConnectionManagerFlatFile100)flatConn.InnerObject;
foreach (DataRow row in dtColumns.Rows)
{
RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 FlatFileColumn100 = ConnectionFlatFile.Columns.Add();
FlatFileColumn100.ColumnType = "FixedWidth";
FlatFileColumn100.ColumnWidth = (int)row["FieldLength"];
FlatFileColumn100.MaximumWidth = (int)row["FieldLength"];
int Scale = (int)row["Scale"];
switch (Scale)
{
case -1:
FlatFileColumn100.DataType = RunTimeWrapper.DataType.DT_WSTR;
break;
default:
FlatFileColumn100.DataType = RunTimeWrapper.DataType.DT_BYTES;
break;
}
RunTimeWrapper.IDTSName100 ColName = (RunTimeWrapper.IDTSName100)FlatFileColumn100;
ColName.Name = (String) row["FieldLevelName"];
}
return flatConn;
}
private PipeLineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
{
_pgk.Executables.Add("STOCK:PipelineTask");
TaskHost _TaskHost = (TaskHost)_pgk.Executables[0];
PipeLineWrapper.MainPipe dataFlowTask = (PipeLineWrapper.MainPipe)_TaskHost.InnerObject;
_TaskHost.Name = ComponentName;
_TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");
return dataFlowTask;
}
private PipeLineWrapper.IDTSComponentMetaData100 AddFileSourceComponent(PipeLineWrapper.MainPipe dataFlowTask, ConnectionManager flaFileCon, String ComponentName)
{
PipeLineWrapper.IDTSComponentMetaData100 FileSource = dataFlowTask.ComponentMetaDataCollection.New();
FileSource.Name = "SourceFile" + ComponentName;
FileSource.ComponentClassID = "DTSAdapter.FlatFileSource.2";
PipeLineWrapper.CManagedComponentWrapper instanceFileSource = FileSource.Instantiate();
instanceFileSource.ProvideComponentProperties();
//Set source connection
FileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flaFileCon.ID;
FileSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(flaFileCon);
//Reinitialize Flat File source metadata,
Reinitiaze(instanceFileSource);
return FileSource;
}
}
}
No comments:
Post a Comment