February 20, 2013 at 3:54 pm
I have a pacakge that pulls the data from the table for a month. It runs on the first of the month and it pulls the previous month of data.
Requirement is to generate files for June 2011 to present but broken them weekly based on the transaction date. How can I run it in a loop per week for that time period?
February 20, 2013 at 4:53 pm
[sarcasm]Simply set the date bucket parameter to weekly and the range filter from June 2011 to today.[/sarcasm]
Sorry, feeling a bit :hehe::w00t:
Truly...
Create a dates table (with weeks in it)
Add a data flow to grab the weeks from the dates table for the required range and feed that into a package variable (recordset destination)
use a foreach loop to loop through the weeks in the variable
feed the start and end dates from the week into your extract routine (where clause)
output to file (use expression to build file name using week number from loop)
If I have assumed too much knowledge and Google doesn't help , then ask questions by all means
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 20, 2013 at 10:04 pm
Thanks. Another question. I have a for each container that reads dates from the table. Inside the container, I've execut SQL task. I did the parameter mapping and seems like I can't access date variable in execute SQL task. Getting syntax error or access violation error. Any idea?
February 21, 2013 at 2:09 am
mister.magoo (2/20/2013)
[sarcasm]Simply set the date bucket parameter to weekly and the range filter from June 2011 to today.[/sarcasm]Sorry, feeling a bit :hehe::w00t:
Truly...
Create a dates table (with weeks in it)
Add a data flow to grab the weeks from the dates table for the required range and feed that into a package variable (recordset destination)
use a foreach loop to loop through the weeks in the variable
feed the start and end dates from the week into your extract routine (where clause)
output to file (use expression to build file name using week number from loop)
If I have assumed too much knowledge and Google doesn't help , then ask questions by all means
Alternatively, add a Conditional Split in the data flow and split the data into four streams - to four separate files.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 21, 2013 at 2:16 am
Phil Parkin (2/21/2013)
Alternatively, add a Conditional Split in the data flow and split the data into four streams - to four separate files.
I like this idea, but only if every month has four weeks in it 😉
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 21, 2013 at 2:19 am
SQL_Surfer (2/20/2013)
Thanks. Another question. I have a for each container that reads dates from the table. Inside the container, I've execut SQL task. I did the parameter mapping and seems like I can't access date variable in execute SQL task. Getting syntax error or access violation error. Any idea?
Can you explain how you are trying to use the variable?
I was thinking that your existing routine would have a date range that it pulled and you would be replacing an existing set of variables with those from the loop (or calling your existing package from a new one and passing in the variables values to existing variables in the original package)...
Are you calling a SP or just using a query ?
More info please?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 21, 2013 at 2:20 am
mister.magoo (2/21/2013)
Phil Parkin (2/21/2013)
Alternatively, add a Conditional Split in the data flow and split the data into four streams - to four separate files.I like this idea, but only if every month has four weeks in it 😉
Well, I did give that some thought.
And there has to be a prior step to set up variables containing the 'break dates' for the month too.
But thought it worth a mention nonetheless.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 21, 2013 at 7:20 am
I have a table which stores date per week. I am passing those dates to my inline statement (in execute sql statement).
February 21, 2013 at 7:33 am
SQL_Surfer (2/21/2013)
I have a table which stores date per week. I am passing those dates to my inline statement (in execute sql statement).
How? Show us please 😀
What error are you getting? Show us the exact error please 😀
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);February 21, 2013 at 1:31 pm
It worked after converted inline statments to stord proc. thanks for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply