Business Problem: Macola business transactions that are redundant and static (no user input required) are currently being performed using tedious manual entry.
Solution: Utilize Wisys objects to perform the Macola transaction automatically within SQL Server using SSIS (SQL Server Integration Services)
Requirements:
- SQL Server 2005 or above with Integration Services installed
- Visual Studio 2005 or above
- VSTA (Visual Studio Tools for Applications) - Installs with Office: http://msmvps.com/blogs/sundar_narasiman/archive/2008/05/20/how-to-install-vsta-visual-studio-tools-for-applications.aspx
- Wisys Objects (Distributed with the Agility Framework)
How-To:
In this example, we are going to create a server job that will run nightly and transfer items that match certain criteria from one location to another.
1) In Microsoft Visual Studio, open a new Integration Services project called "AutoTransfer"
2) First, rename the package to "AutoTransfer" by clicking on "Package.dtsx" in the solution explorer and then changing the file name field to "AutoTransfer.dtsx".
Next, click the "Data Flow" tab at the top of the screen. You should see the text: "No Data Flow tasks have been added to this package. Click here to add a new Data Flow task." Click the link to create a new task.
3) Now we want to generate the data for our transaction (find the items that need to be transferred). We will use an OLE DB Source to run a SQL query to generate the data.
Drag an OLE DB Source from the Toolbox (on the left of the screen) on to the middle of the screen. Right click on the new "OLE DB Source box" showing on the screen and choose "Edit" to bring up the OLE DB Source Editor.
4) From the OLE DB Source Editor screen, click the "NEW" button to link the package to a new OLE DB connection manager. On the next screen, click the "NEW" button again to create a new connection for the link. Enter the relevant server name, database, and click OK.
On the connection manager screen, highlight the new server connection we have just created and click OK.
5) On the OLE DB Source Editor screen, select the drop down menu for Data Access Mode and choose "SQL Command". In the Query screen below it, paste in the SQL query to generate our data. In this example we use:
SELECT ord_no AS [Transfer], item_no AS Item, doc_ord_no AS [Order],
CAST(quantity as INT) AS [Qty], GETDATE() AS [trx_dt], N'' AS [PostError]
FROM dbo.iminvtrx_sql
WHERE trx_dt > DATEADD(day,-1,GETDATE()) AND doc_type = 'T' AND LTRIM(comment)
= 'GD' and lev_no = 0
This statement will find all transfers initiated today that are flagged with "GD" in the comment.
6) After pasting the SQL query into the connection manager window, click OK to return to the Data Flow screen.
Now that we have gathered our data for the transfers, we will create a script component to call the Wisys object and perform the transfers. Drag the "Script Component" object from the Toolbox on the left and place it under the OLE DB Source object on the Data Flow. After placing the component, a window will pop prompting you to choose a type for the script component. Choose "Transformation".
7) Click the OLE DB Source object and notice that a green arrow appears. This isthe data flow path that determines where the data from our SQL query will flow. Click the green arrow and point it to the Script Component object.
Next, we need to tell the Script Component to use the data we just connected to it. Right click on the script Component object and choose "Edit". Go to the "Input Columns" menu and place a check next to every data input listed. Also, we need to change the UsageType of the "PostError" variable to "ReadWrite".
8) Now, we are ready to dive into the C# code that will call the Wisys object and perform the transfer.
Return to the Script menu option and click "Edit Script". A new window will appear: "ssisscript - Integration Services Script Component". This is actually a "mini-IDE" that utilizes VSTA to allow a developer to create C# or VB.NET code within their SSIS package.
In order to call our Wisys objects, we must add a reference to them in our new SSIS script. While still in the SSISSCRIPT window, right-click the References folder under the Project Explorer and choose "Add Reference". Click the "Browse" tab and navigate to your Wisys Bin folder. This is where all of the Wisys objects are stored that we can add to our project to perform Macola transactions.
For our transfer example, we need "Wisys.ImTrx.v3.0.dll" which contains the transfer object and "Wisys.AllSystem.v3.0.dll" which contains supporting objects required by all transactions.
9) After clicking OK and adding both DLL's to the project, you should see both of them listed in Project Explorer under References. If they are visible, they are now ready to be called programmatically.
In your code, first delete the "Post Execute" and "Pre Execute" methods as they will not be needed.
Next, at the top of the "ProcessInputRow" method, declare the following:
//Local Variables
string strData = "";
string strErrorcodepost = "";
DataTable dtTable = new DataTable();
//Wisys Objects
Wisys.AllSystem.cReader dbReader = new Wisys.AllSystem.cReader();
Wisys.AllSystem.TrxEnums.DatabaseType dbtype = new Wisys.AllSystem.TrxEnums.DatabaseType();
dbtype.GetType();
Wisys.AllSystem.ConnectionInfo conn = new Wisys.AllSystem.ConnectionInfo();
Wisys.AllSystem.cSqlProcessor sqlproc = new Wisys.AllSystem.cSqlProcessor(dbtype);
Wisys.ImTrx.Transfer trans = new Wisys.ImTrx.Transfer();
Wisys.AllSystem.TrxEnums.TransactionAction trx = new Wisys.AllSystem.TrxEnums.TransactionAction();
10) Before we dig into the actual transaction, we need to configure the properties of the Wisys Connection method to match our server and database, open the Wisys connection after it has been configured, and then pass the opened connection to the transfer object:
//Configure Wisys Connection Method
conn.Parameters("HQSQL", "001");
conn.SqlProcessor = sqlproc;
//Open Wisys Connection
conn.OpenWisysConnection(true, ref strData);
//Pass Connection to Transfer Object
trans.Connection(ref conn);
11) Next, we set the relevant properties of the Transfer object. In our example, some of the fields are dynamic and change each day so they need to be derived from our SQL query dataset: item, quantity, and sales order reference ("DocOrderNo"). Other fields are static and will be the same every time we run the transfer job: the comment fields, username, and To/From locations. The dynamic fields need to bet set to our Row data derived from our SQL query and the static fields can be hard-coded within the Script Component:
//Set Properties of Transfer Object
trans.ItemNumber = Row.Item;
trans.Quantity = Row.Qty;
trans.ToLocation = "GD";
trans.FromLocation = "IT";
trans.UserName = "HQSQL";
trans.Comment1 = "SQL JOB: Auto_Transfer_In_GD";
trans.Comment2 = "SQL JOB: Auto_Transfer_In_GD";
trans.DocOrderNo = Row.Order;
FROM dbo.iminvtrx_sql
WHERE trx_dt > DATEADD(day,-1,GETDATE()) AND doc_type = 'T' AND LTRIM(comment)
= 'GD' and lev_no = 0
This statement will find all transfers initiated today that are flagged with "GD" in the comment.
6) After pasting the SQL query into the connection manager window, click OK to return to the Data Flow screen.
Now that we have gathered our data for the transfers, we will create a script component to call the Wisys object and perform the transfers. Drag the "Script Component" object from the Toolbox on the left and place it under the OLE DB Source object on the Data Flow. After placing the component, a window will pop prompting you to choose a type for the script component. Choose "Transformation".
7) Click the OLE DB Source object and notice that a green arrow appears. This isthe data flow path that determines where the data from our SQL query will flow. Click the green arrow and point it to the Script Component object.
Next, we need to tell the Script Component to use the data we just connected to it. Right click on the script Component object and choose "Edit". Go to the "Input Columns" menu and place a check next to every data input listed. Also, we need to change the UsageType of the "PostError" variable to "ReadWrite".
8) Now, we are ready to dive into the C# code that will call the Wisys object and perform the transfer.
Return to the Script menu option and click "Edit Script". A new window will appear: "ssisscript - Integration Services Script Component". This is actually a "mini-IDE" that utilizes VSTA to allow a developer to create C# or VB.NET code within their SSIS package.
In order to call our Wisys objects, we must add a reference to them in our new SSIS script. While still in the SSISSCRIPT window, right-click the References folder under the Project Explorer and choose "Add Reference". Click the "Browse" tab and navigate to your Wisys Bin folder. This is where all of the Wisys objects are stored that we can add to our project to perform Macola transactions.
For our transfer example, we need "Wisys.ImTrx.v3.0.dll" which contains the transfer object and "Wisys.AllSystem.v3.0.dll" which contains supporting objects required by all transactions.
9) After clicking OK and adding both DLL's to the project, you should see both of them listed in Project Explorer under References. If they are visible, they are now ready to be called programmatically.
In your code, first delete the "Post Execute" and "Pre Execute" methods as they will not be needed.
Next, at the top of the "ProcessInputRow" method, declare the following:
//Local Variables
string strData = "";
string strErrorcodepost = "";
DataTable dtTable = new DataTable();
//Wisys Objects
Wisys.AllSystem.cReader dbReader = new Wisys.AllSystem.cReader();
Wisys.AllSystem.TrxEnums.DatabaseType dbtype = new Wisys.AllSystem.TrxEnums.DatabaseType();
dbtype.GetType();
Wisys.AllSystem.ConnectionInfo conn = new Wisys.AllSystem.ConnectionInfo();
Wisys.AllSystem.cSqlProcessor sqlproc = new Wisys.AllSystem.cSqlProcessor(dbtype);
Wisys.ImTrx.Transfer trans = new Wisys.ImTrx.Transfer();
Wisys.AllSystem.TrxEnums.TransactionAction trx = new Wisys.AllSystem.TrxEnums.TransactionAction();
10) Before we dig into the actual transaction, we need to configure the properties of the Wisys Connection method to match our server and database, open the Wisys connection after it has been configured, and then pass the opened connection to the transfer object:
//Configure Wisys Connection Method
conn.Parameters("HQSQL", "001");
conn.SqlProcessor = sqlproc;
//Open Wisys Connection
conn.OpenWisysConnection(true, ref strData);
//Pass Connection to Transfer Object
trans.Connection(ref conn);
11) Next, we set the relevant properties of the Transfer object. In our example, some of the fields are dynamic and change each day so they need to be derived from our SQL query dataset: item, quantity, and sales order reference ("DocOrderNo"). Other fields are static and will be the same every time we run the transfer job: the comment fields, username, and To/From locations. The dynamic fields need to bet set to our Row data derived from our SQL query and the static fields can be hard-coded within the Script Component:
//Set Properties of Transfer Object
trans.ItemNumber = Row.Item;
trans.Quantity = Row.Qty;
trans.ToLocation = "GD";
trans.FromLocation = "IT";
trans.UserName = "HQSQL";
trans.Comment1 = "SQL JOB: Auto_Transfer_In_GD";
trans.Comment2 = "SQL JOB: Auto_Transfer_In_GD";
trans.DocOrderNo = Row.Order;
12) Now that our properties are set, we can post the transaction and close the Wisys session. We will also return any error messages generated by the posting to our dataset so that we can then write it to our logfile:
//Post Transfer Object
trans.PostTrx(ref strErrorcodepost);
//Write Post Error to Dataset
if (strErrorcodepost == "")
{
Row.PostError = "SUCCESS";
}
else
{
Row.PostError = strErrorcodepost;
}
//Close SQL Connection
conn.CloseWisysConnection(trx, ref strData);
trans.PostTrx(ref strErrorcodepost);
//Write Post Error to Dataset
if (strErrorcodepost == "")
{
Row.PostError = "SUCCESS";
}
else
{
Row.PostError = strErrorcodepost;
}
//Close SQL Connection
conn.CloseWisysConnection(trx, ref strData);
13) Our Script Component is now complete. Save and exit the VSTA screen to return to the Script Transformation Editor window. Click "OK" to close and save and we now return to the Data Flow screen.
Drag a "Flate File Destination" component from the Toolbox on to the Data Flow. Connect the data flow path (green arrow) from the Script Component to the new Flate File Destination component.
14. Double click the Flat File Destination component and then click the "NEW" button to create a new flate file connection. When prompted for a file type, choose "Delimited". Next, enter "AutoTransfer Log" for the connection name and description. Check the "Column Name in First Row" box.
15. The package is complete. Choose save project, then open SSMS (SQL Server Management Studio) and connect to your integration services server. Once there, right click on "MSDB" and choose "import package".
On the "Import Package" screen, choose "File System" as your package location then point the package path to the AutoTransfer.dtsx file that you just saved. Name the package "AutoTransfer".
Click OK to import the package.
16. Next, open the SQL Server Agent, then right click and choose "New Job". Name the job and then under "Steps" choose "New" to create a new step. Name the step, change Type to "SQL Server Integration Services Package", enter the server name, and then select the package from the list.
17. Last, schedule the job to run when needed (in our example, it will run nightly) and you're done!
Each evening the job will run and any items which match the criteria in our SQL query will be transferred to the location specified in our SSIS script. When done, the job will write a log of what was transferred to the AutoTransfer.log in the \bin\ directory of our SQL server. No more redundant and wasteful manual transfers!
