SQLTeam.com | Weblogs | Forums

How to create dynamic excel sheets based on conditions in ssis


#1

Hi I have one doubt in ssis, how to create dynaimic excel sheets based daily running using below conditions : conditions: if current date(systemdate) dayname is monday then i want load previouse year data(currentyear-1) and should be

create dynamic monthandyear sheetname and corresponding data: example: suppose today is monday then my packge should be load only pervious year data in the excel file and excel file should be contain monthandyearsheetsofpreviousdata. here:excelfilename is :2017 and sheetsname:jan2017..>this should contain only jan2017 related data,2nd sheet for feb2017: this file only feb2017 related data only, for march2017 sheet:its should contain only march2017 data similary upto dec2017 sheets create dynamicaly.

if current date(systemdate) dayname is tuesday then want create dynamic sheet for previous month data of current year:

example: supuose today is tuesday then i want load data only previous month of currentyear data into the excel sheet:that means we need load only january2018 data only in excel file.

if current date(systemdate) dayname is Wednesday then want create dynamic sheet for previous last 3 months data from current date: example:upuose today is Wednesday then i want create excel sheets dynamicaly only for last 3 months of current data sheets : Jan2018,dec2017,nov2017 sheets with corresponding data.

if current date(systemdate) dayname is Thursday then want create dynamic sheet for previous year of may month data only : example:upuose today isThursday then i want create excel sheets dynamicaly only for may month data sheet: may2017 sheets with corresponding data.

if current date(systemdate) dayname is Friday then want create dynamic sheet for previous year related August and september and December months data : example:upuose today is Friday then i want create excel sheets dynamica only for previous 3 required months data sheets: Aug2017,sep2017,Dec2017 sheets with correspondin data.

if current date(systemdate) dayname is Saturday then want create dynamic sheet for previous year related August month data : example:upuose today is Friday then i want create excel sheets dynamica only for previous August required months data sheets: Aug2017 correspondin data. please tell me how to achive this task in ssis:

souce table: emp

CREATE TABLE [dbo].[emp](
[country] varchar NULL,
[empname] varchar NULL,
[doj] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'INDIA', N'hari', CAST(N'2017-10-12' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'INDIA', N'BANU', CAST(N'2017-11-12' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'INDIA', N'JAI', CAST(N'2017-09-15' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'USA', N'JAIA', CAST(N'2017-09-19' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'CHEN', N'H', CAST(N'2017-08-06' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'AUS', N'YN', CAST(N'2017-07-04' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'SA', N'TN', CAST(N'2017-06-02' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'INDIA', N'UN', CAST(N'2017-05-22' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'USA', N'F', CAST(N'2017-04-02' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'AUS', N'TN', CAST(N'2017-02-28' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'USA', N'YN', CAST(N'2017-03-03' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'CHEN', N'YE', CAST(N'2017-12-25' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'CHEN', N'UI', CAST(N'2017-12-29' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'INDIA', N'YE', CAST(N'2018-01-12' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'USA', N'TB', CAST(N'2018-02-07' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'CHEN', N'RE', CAST(N'2018-01-19' AS Date))
GO
INSERT [dbo].[emp] ([country], [empname], [doj]) VALUES (N'USA', N'TE', CAST(N'2017-01-29' AS Date))
I trield like below:

useing foreachadoenutrator task.but its not working.can you please tell me how to achive this task in ssis.