Do you have daily imports for Microsoft Dynamics SL that you wish you could automate? Maybe you’ve inquired about automating them, and were told “if you had the Application Server, you could automate it then.”
Well, good news: you don’t need the Application Server module. You can create a simple process for automating an import into Dynamics SL using these tools:
- Control file
- Data file
- Batch file
- Windows Task Scheduler
Here’s an example of how I set up an automated import process for a customer.
Control File
It starts off with creating your control file. This is the file that tells Dynamics SL how the import process should “see” the data being imported. Here is a simple example of a control file that will import a Journal Transaction.
| ‘ControlMacroType: VBAComplete
‘ VBComponent: ThisScreen, ComponentType: 100 ********************************************************** ‘01010 Control Macro Sub ProcessImportLine( LevelNumber%, Retval% ) select Case LevelNumber case TI_Start
call AliasConstant( “Level0”, “Batch” )
call AliasConstant( “Level1”, “Detail” )
case 0 ‘ 0 of 1
‘Level 0 if of Type N serr = SetObjectValue( “cbatnbrH”, ImportField(1) )
case 1 ‘ 1 of 1
‘Level 1 if of Type D serr = SetObjectValue( “cacct”, ImportField(1) ) serr = SetObjectValue( “cprojectid”, ImportField(2) ) serr = SetObjectValue( “ctaskid”, ImportField(3) ) serr = SetObjectValue( “csub”, ImportField(4) ) serr = SetObjectValue( “crefnbr”, ImportField(5) ) serr = SetObjectValue( “ctrandate”, ImportField(6) ) serr = SetObjectValue( “cdramt”, ImportField(7) ) serr = SetObjectValue( “ccramt”, ImportField(8) ) serr = SetObjectValue( “ctrandesc”, ImportField(14) )
case TI_Finish
End Select End Sub
‘ End VBComponent ********************************************************** |
Based on how your control file is set to read the data (i.e. in what order the fields are laid out), you will next need to create your data file.
Data File
The data file is a comma-delimited (Comma Separated Values or .csv) file. You can create a .csv file in Excel. When you’re in Excel, choose the Save As option of Comma Delimited. However, if you need to go back into the data file, reopen it in Notepad, not Excel. Why? If you reopen it in Excel, you will chance removing the leading zeros on text fields (i.e. “0000” subaccount will convert to “0”).
The default extension for the transaction import data file import is “.dta,” but is not required. I recommend sticking with “.dta” so you can set the default application as Notepad versus “.csv” which Excel will open automatically. Here is my sample data file.
| LEVEL0,,,,,,,,,,,,,,,,,,,,,,
LEVEL1,7050,0,,”00-000-00-00-00-0″,”TEST”,12/31/2017,10,0,description, LEVEL1,7050,0,,”00-000-00-00-00-0″,”TEST”,12/31/2017,0,10,description, LEVEL1,7050,0,,”00-000-00-00-00-0″,”TEST”,12/31/2017,10,0,description, LEVEL1,7050,0,,”00-000-00-00-00-0″,”TEST”,12/31/2017,0,10,description, LEVEL1,7050,0,,”00-000-00-00-00-0″,”TEST”,12/31/2017,10,0,description, LEVEL1,7050,0,,”00-000-00-00-00-0″,”TEST”,12/31/2017,0,10,description, |
Import Batch File
Now that you have a control file and a data file created, you need an import batch file. In my example, I am first validating that the data file exists and if it does, it will run the import process.
if exist “C:\Imports\TI\0101000.dta” (
“C:\Program Files (x86)\Microsoft Dynamics\SL\Applications\GL\0101000.exe” [TI]TM=3 [TI]TC=”C:\Imports\TI\0101000.ctl” [TI]TD=”C:\Imports\TI\0101000.dta” [TI]TO=”C:\Imports\TI\0101000.log” [TI]TL=2 [TI]TE=1 [TI]Minimize=N
)
Note: I added in line breaks above for purposes of this post. However, the actual command within the “.bat” file has a specific layout. If you select to add additional line breaks, you will have to add additional commands to handle the text breaks. Here’s how it should be laid out – no line breaks.
Windows Task Scheduler
Once you have the batch file created, you can create a task within the Windows Task Scheduler to run your import process at a set time and date interval.
When the task scheduler runs the process, you will see a “DOS” window open up and the batch file initiated.
If Dynamics SL is not currently logged in, the task scheduler will launch the application automatically. Since the batch file is calling the Transaction Import process programmatically, you will see the Journal Transaction screen launch and a new Journal Transaction batch created.
Note: If you are not using Windows Authentication, you will need to need to additional steps for logging in the user.
Here are my sample files for you to download. I hope this helps automate imports for you, too. If you have questions or need some assistance, visit our support page for more help.
