Logic and SSIS Package Tasks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Logic and SSIS Package Tasks

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2008-07-14 : 22:48:19

1. There are 2 Data Files in the MPG Subscription feed. The files and file schemas are:
Delta Files:
XBXAccountYYYYMMDD_001_Delta.txt
XBXSubYYYYMMDD_001_Delta.txt

Full Files:
XBXAccountYYYYMMDD_Full_001.txt
XBXSubYYYYMMDD_Full_001.txt

2. The MPG subscription files are Unicode Text files using “TAB” column delimiter and “NEWLINE” row delimiter.

3. Subscription files schedule FULL extract occurs on EVERY Calendar Month Day “1” (e.g. Jan-1, Feb-1, Mar-1, Apr-1, …). Files are created early AM and can be expected to be available by 12:00 PM of the day of extract.

4. Subscription files schedule DELTA extract occurs EVERY Monday of week (e.g. Jul-3, Jul-10, Jul-17, Jul-24, Jul-31, Aug-7, …). Files are created early AM and can be expected to be available by 12:00 PM of the day of extract

My task scenario has discussed above, so can any one let me know how to write the logic and what SSIS Tasks need to be used for extracting FULL data on 1st of each month and DELTA data in every week on Monday,

and how can i automate this process on every month and every week?

Please let me know the answer, it would be soo helpful.

-Thanks N Regards,
Kanthi.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 12:02:05

1.You need a flat file source connection to get data from the files. You also need a variable or two to store the filename which is generated automatically each time by appending the dates.The filename property of connection can be mapped to variable using dynamic properties task.
2.The delimeiters can be set inside the file connection properties in data flow task
3.You might need an expression evaluation step in constraint to check if current date is 1 or if its a monday and based on result link to two data flow tasks which does either full or delta extraction
4.Schedule a job to execute SSIS only on 1st day of month or all mondays

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources