extract all data with week

Question

extract all data with week

samirca007

SSC-Addicted

Points: 417
More actions
March 10, 2020 at 3:23 pm

#3732863

Good morning all
I have a table that contains the history of a table over 1 year old he asked me to extract one file per week
so we total I must have 54 files Who can help me with this need please

Viewing 15 posts - 1 through 15 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Lots of us can help you, but you need to help us help you. What have you completed so far? Why isn't what you've done working, or where have you got stuck?

P.s. who is"he"?

This reply was modified 4 years, 8 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Did 'he' say whether this is a one-off requirement, or something which will need to run periodically?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Phil Parkin wrote:

Did 'he' say whether this is a one-off requirement, or something which will need to run periodically?

I can't help in SSIS but I try to code "one-offs" such as this as if the requirement will appear again... and it usually does.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 4

How about a CREATE TABLE script and maybe some sample data?

samirca007 SSC-Addicted Points: 417 More actions · Answer 5

samirca007

SSC-Addicted

Points: 417

March 11, 2020 at 12:12 pm

#3733167

attached my package

Thom A SSC Guru Points: 99130 More actions · Answer 6

samirca007 wrote:

attached my package

Attaching the package (not that you actually have) with no explanation isn't going to really help on it's own. Especially when we won't be able to run said package without the project objects, sample data (be that a file or Database table), or the results you expect from that sample.

Help us help you, and give us all the information we need.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

samirca007 wrote:

attached my package

To what? Certainly not this post.

If you are not prepared to answer any of the questions which people are directing at you, don't be surprised at the lack of help you receive in return. This is a two-way street.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

One of you folks that knows how to use SSIS should write an article on this... it would be a popular one because a lot of people need to do this type of thing. Who knows... I might follow that up with an "alternative" to SSIS. Feel free to crib the test data generator below if you do.

With that in mind, I wouldn't mind seeing a demo of what you need to go through on this. It's not likely the OP will spend any time on this, so here's a test table that you can use. It takes 19 seconds to form on NVME SSDs and about twice that time on SATA spinning rust.

--===== If the test table already exists, drop it to make reruns in SSMS easier.
     -- The GOs are to make it so you don''t get errors if you need to add or delete columns.
     IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL
   DROP TABLE dbo.JBMTest
;
GO
--===== Create and populate a large test table on-the-fly.
     -- "SomeInt" has a range of 1 to 50,000 numbers
     -- "SomeLetters2" has a range of "AA" to "ZZ" 
     -- "SomeDecimal" has a range of 10.00 to 100.00 numbers
     -- "SomeDate" has a range of >=01/01/2010 & <01/01/2030 whole dates
     -- "SomeDateTime" has a range of >=01/01/2010 & <01/01/2030 Date/Times
     -- "SomeRand" contains the value of RAND just to show it can be done without a loop.
     -- "SomeHex9" contains 9 hex digits from NEWID()
     -- "SomeFluff" is a fixed width CHAR column just to give the table a little bulk.
 SELECT TOP 10000000
         SomeInt        = ABS(CHECKSUM(NEWID())%50000) + 1
        ,SomeLetters2   = CHAR(ABS(CHECKSUM(NEWID())%26) + 65)
                        + CHAR(ABS(CHECKSUM(NEWID())%26) + 65)
        ,SomeDecimal    = CAST(RAND(CHECKSUM(NEWID())) * 90 + 10 AS DECIMAL(9,2))
        ,SomeDate       = DATEADD(dd, ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'2010','2030')), '2010')
        ,SomeDateTime   = DATEADD(dd, DATEDIFF(dd,0,'2010'), RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2010','2030'))
        ,SomeRand       = RAND(CHECKSUM(NEWID()))  --CHECKSUM produces an INT and is MUCH faster than conversion to VARBINARY.
        ,SomeHex9       = RIGHT(NEWID(),9)
        ,SomeFluff      = CONVERT(CHAR(170),'170 CHARACTERS RESERVED') --Just to add a little bulk to the table.
   INTO dbo.JBMTest
   FROM      sys.all_columns ac1 --Cross Join forms up to a 16 million rows
  CROSS JOIN sys.all_columns ac2 --Pseudo Cursor
GO
--===== Add a non-unique Clustered Index to SomeDateTime for this demo.
 CREATE CLUSTERED INDEX IXC_Test ON dbo.JBMTest (SomeDateTime ASC)
;
--===== Display the generated limits of the data in the SomeDateTime column.
 SELECT LoDateTime = MIN(SomeDateTime), HiDateTime = MAX(SomeDateTime) FROM JBMTest
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 9

Ah... and in the absence of a more defining problem, given a year number, create one file per ISO week that contains all columns of each row in the week as a Tab Separated (TSV) File with column headers that use the same delimiters (Tabs). If you want to get clever, show how to do it not only for TSV but CSV and fixed width formats, as well.

Again, I think an article on this would be quite popular and good stock to add to your resume.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GaryV Hall of Fame Points: 3307 More actions · Answer 10

Not an article, but here's a short outline of what an SSIS package to do this would look like.

Create a connection manager for the output file, with a generic name.
Set up variables:
- One to hold the date (see below in the foreach loop)
- One for the actual file name - use an expression to include the date variable
Use Properties of the connection manager to assign the specific file name.
Create a query that gives the list of dates for which files are desired. Put the result set into an object.
Use a foreach loop with that result set as the enumerator of the collection. In that loop:
- Assign the date from the enumerator to the date variable created above. This will change the expression used for the file being generated.
- Create a data flow with a query that pulls the desired data for the date, and sends it to the connection manager

If you don't understand these steps, I'd suggest finding a tutorial on SSIS that includes setting variables, ForEach and Connection Managers.

This reply was modified 4 years, 8 months ago by GaryV.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

GaryV wrote:

If you don't understand these steps, I'd suggest finding a tutorial on SSIS that includes setting variables, ForEach and Connection Managers.

That's why I'm asking people to write a definitive article for this on this site... a lot of people summarize steps like these but they don't help beginners because steps like "Create a data flow with a query that ... " might as well be written as "<insert miracle here>".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

Jeff Moden wrote:

GaryV wrote:
If you don't understand these steps, I'd suggest finding a tutorial on SSIS that includes setting variables, ForEach and Connection Managers.
That's why I'm asking people to write a definitive article for this on this site... a lot of people summarize steps like these but they don't help beginners because steps like "Create a data flow with a query that ... " might as well be written as "<insert miracle here>".

I know what you mean. To go into that level of detail for a requirement such as this would create a very long article, however. I wonder what percentage of people would make it all the way through. A similar number to those who made it to the end of A Brief History of Time, I suspect, in today's ADD world.

It's always interesting how different developers approach the same problem. My (imagined) solution bears a fair resemblance to that described above, but it would not be the same. For performance reasons, I avoid querying SQL Server when I can, so I would not do this, for example: "Create a query that gives the list of dates for which files are desired. Put the result set into an object." Instead, I'd use functions and calls within SSIS to achieve a similar outcome.

I would not have the confidence in my abilities to call any solution I create 'definitive'. Instead, I create stuff which works as quickly and elegantly as my knowledge allows. There are many smarter developers out there whose own solutions would be 'more definitive' than mine.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 13

I appreciate your humility, Phil, but I think Kruger-Dunning is in effect and you're drastically underrating your skills both in SSIS and as a writer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GaryV Hall of Fame Points: 3307 More actions · Answer 14

Phil Parkin wrote:

Jeff Moden wrote:
GaryV wrote:
If you don't understand these steps, I'd suggest finding a tutorial on SSIS that includes setting variables, ForEach and Connection Managers.
That's why I'm asking people to write a definitive article for this on this site... a lot of people summarize steps like these but they don't help beginners because steps like "Create a data flow with a query that ... " might as well be written as "<insert miracle here>".
I know what you mean. To go into that level of detail for a requirement such as this would create a very long article, however. I wonder what percentage of people would make it all the way through. A similar number to those who made it to the end of A Brief History of Time, I suspect, in today's ADD world.
It's always interesting how different developers approach the same problem. My (imagined) solution bears a fair resemblance to that described above, but it would not be the same. For performance reasons, I avoid querying SQL Server when I can, so I would not do this, for example: "Create a query that gives the list of dates for which files are desired. Put the result set into an object." Instead, I'd use functions and calls within SSIS to achieve a similar outcome.
I would not have the confidence in my abilities to call any solution I create 'definitive'. Instead, I create stuff which works as quickly and elegantly as my knowledge allows. There are many smarter developers out there whose own solutions would be 'more definitive' than mine.

Given the OP's lack of response, I don't know if this is still being sought. But I agree with Phil, without an example to use, a generic article on how to do something like this would get quite intensive.

And yes, there's many ways to skin this cat.