Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ADP transfer to GL Transaction file 1
- Thread starter Lorain
- Start date
-
1
- #2
- Thread starter
- #3
Thanks for the qick response. The person from ADP said she thinks that they can. I am waiting to find out what export options are really available. If exporting to text is an option, then what step would be next to match departments to the appropiate GL account numbers, etc.?
Thank in advance.
Thank in advance.
Without really knowing the intimate details of your accounting system, I can only give a generalized answer.
What I would do is create a few "real" tables, not a temp tables since it seems this will be a continual process.
The first table to create would be a work table that you may want to call something like ADPImport. The fields in this table should match the fields the ADP file has. We can now import the text file into this ADPImport table either by DTS or OSQL. I prefer DTS since you can manipulate and port the data in various fashions to fit your needs. It is also much more powerful than an OSQL command(s).
Now come the part on setting up the records to match your accounts. Here I cannot help because I am not familiar with your system.
In the most general form, you will use sets of INSERT INTO ... SELECT FORM statements to grab the records based on a GL Code and insert them into your transtable.
You can accomplish all of the needed tasks within a DTS package. The issue at hand is having someone versed in your accounting process who knows a bit about SQL Server and DTS.
The other table I probably would create would be a history table. Maybe ADPImportHistory. This table would match the ADPImport table exactly except maybe a datetime field, if once does not already exist, for the date we imported the records. After your process is complete, you could export the ADPImport table to the ADPImportHistory table, set the import date field to the run date, and then you could have a complete history of the transactions that were imported into your accounting system.
Sorry for not going into too much detail, but I just wanted to get across the basic idea of what needs to be accomplished. Hope this help some. Good Luck!
Thanks
J. Kusch
What I would do is create a few "real" tables, not a temp tables since it seems this will be a continual process.
The first table to create would be a work table that you may want to call something like ADPImport. The fields in this table should match the fields the ADP file has. We can now import the text file into this ADPImport table either by DTS or OSQL. I prefer DTS since you can manipulate and port the data in various fashions to fit your needs. It is also much more powerful than an OSQL command(s).
Now come the part on setting up the records to match your accounts. Here I cannot help because I am not familiar with your system.
In the most general form, you will use sets of INSERT INTO ... SELECT FORM statements to grab the records based on a GL Code and insert them into your transtable.
You can accomplish all of the needed tasks within a DTS package. The issue at hand is having someone versed in your accounting process who knows a bit about SQL Server and DTS.
The other table I probably would create would be a history table. Maybe ADPImportHistory. This table would match the ADPImport table exactly except maybe a datetime field, if once does not already exist, for the date we imported the records. After your process is complete, you could export the ADPImport table to the ADPImportHistory table, set the import date field to the run date, and then you could have a complete history of the transactions that were imported into your accounting system.
Sorry for not going into too much detail, but I just wanted to get across the basic idea of what needs to be accomplished. Hope this help some. Good Luck!
Thanks
J. Kusch
- Thread starter
- #5
Great. . I will find out tomorrow what export options i have with ADP and then go from there. I can create the SQL tables as you suggested. I have worked some with MSSQL and I know the Macola fields for Gl etc. I know enough to get that setup. I am unsure how to setup an executable, is that where the DTS package comes in? Can you help me with that part?
Lorain ...
While you are waiting for an answer from ADP, go ahead and get into Enterprise Manager(EM). IN there you will find a tab called Data Transformation Services(DTS). Right-click on that Local Packages tab and choose to create a new package. Take a quick peak at the Connection and Task objects you will be able to use in creating your conversion/insertion package.
We can set the source connection as your ADP text file, and the your target connection as the server where we created the tables to import in.
Once the connections are established, we may start w/ a "Transform Data Task" task to import the text file into the table. We could then do a couple "Execute SQL Task" tasks to manipulate the table data with a GL code that applies. We may have to add another field to our ADPImport table to handle this code if it is not w/in out text import file.
Then we could create another task that inserts our newly aligned data into the TransTable and Bingo! We are there.
As you can see in DTS, you can do ALOTT of tasks and data manipulations. Heck, you can even email a message when the package finishes successfully or heaven forbid ... it FAILS - haha.
SO take a good look at the tools at your disposal w/in DTS first. Then we will go from there.
Thanks
J. Kusch
While you are waiting for an answer from ADP, go ahead and get into Enterprise Manager(EM). IN there you will find a tab called Data Transformation Services(DTS). Right-click on that Local Packages tab and choose to create a new package. Take a quick peak at the Connection and Task objects you will be able to use in creating your conversion/insertion package.
We can set the source connection as your ADP text file, and the your target connection as the server where we created the tables to import in.
Once the connections are established, we may start w/ a "Transform Data Task" task to import the text file into the table. We could then do a couple "Execute SQL Task" tasks to manipulate the table data with a GL code that applies. We may have to add another field to our ADPImport table to handle this code if it is not w/in out text import file.
Then we could create another task that inserts our newly aligned data into the TransTable and Bingo! We are there.
As you can see in DTS, you can do ALOTT of tasks and data manipulations. Heck, you can even email a message when the package finishes successfully or heaven forbid ... it FAILS - haha.
SO take a good look at the tools at your disposal w/in DTS first. Then we will go from there.
Thanks
J. Kusch
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question