I have DTS Documentation, But Help me to Implement... 1

[sqldevrequiretektips]

I have DTS Process documentation now i have to complete the task. I am Learning DTS. So please tell me how should i complete the task. How would i start?

( Also It has 48 hours deadline so...any help would really appriciate...)

Please review documentation and help me as much you can...
Thanks....

See Following Documetation : -
DTS Process Run 1st Monday of each Month

Global Variables: Start Date, End Date
Start Date: Current Date
End Date: End Date of current Month

·Get period Info with week no from table name DB033985.BIAL_ACCTGWKTB (DB2 Server Table) and insert into PeriodInfo Table (SQL Server Table)

·Insert data in tblWIActcat_WIDay, tblWIEmpType_WIDay (SQL server Tables) from
Cust_ Modtb(SQL Server table) and tblWICustInfo(SQL Server Table)
Before insert data in tblWIActCat_WIDay
And tblWIEmp_Type_WIDay update 1,0 with M O D

· Execute store procedure which insert records from tblWIActCatwiDay,tblWiEmpType_WIDay to tblWeeklyMODTemplate (SQL Server tables).

 

[SQLSister]

DTS is a pretty complex suject. Suggest you read the forum dedicated to it, forum961. And read about how to create DTS packages in BOL. Then get a good book on DTS. This FAQ has some recommendations.

First step is to flow chart what actions you need to take in the DTS package. Usually, when I import from another source, especially if it doesnot have the same structure and the data needs manipulating, I bring the data to a holding table and tehn run execute SQL tasks against that table to do the data manipulation. YOu can put all these steps in one package.

From your other question,there are already existing DTS packages on your system. I would go into them and figure out what they are doing as a first step to making my own. Once you have seen a few, they make more sense and you will be familair with the way your company generally does such tasks.



Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sqldevrequiretektips]

I have to complete this task before the deadline so..if you could help me out to complete each task...would be really appriciate...

As you mentioned me in your previous reply...that bring the data to holding table then run excute SQL tasks againgst that table to do manipulation....

if you could explain me in detail....would be really helpful...or just give me an example...

Thank you so much.......

ND

 

[SQLSister]

Well DTS is a graphical interface, so it's not like I can give you code. First, do all of this on the development server, you are likely to make mistakes while you learn, yo don;t want o mess up production. But let's see.

In DTS, the first thing you need to do is set up your connections, one for the source of the data and one for the destination for the data. You will see the icons to set these up in the upper left corner. YOu proabably want to drag the first icon over and then fill in the resulting form as to the location of the database and type of database it is.

To do the import to the holding table, you need a task to be performed. Probably inthis case the Transform Data task which will ask you which of your two connections is the source and which is the destination. If the table does not exist onthe server, this task will creat it on the destination server. Since you are not familar with the databses in question, bring it into a table with the same structure as the source is best to start with.

Do this much and save and run the package. Then go look at your data as it imported and see what you need to do to it to get it into the format you need to update or insert into the existing table. Once you can really see the data, then write regular t-SQl staments to perform inserts and updates to the table you are importing to fromthe holding table. Once you have these perfected, then take that code and put it into an Execute SQL task inthe original package. You may need multiple ones if you want to have differnt failure poitns. Comnnect the execute SQL task to the second connection Icon using the ON success connection. You will find this under workflow. You selct both objects inthe order you want the tasks to flow (first task first, then second task and click the workflow onsuccess menu item to do this).


Hope this helps you get started.


Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[sqldevrequiretektips]

That's really help me lot.....

Thank you so much......
ND