as an effort to troubleshoot this query, I modified it to:

WITH PivotData AS

(

select [ProjectWork Number], WeekNO, WeekValue From dbo.staging

)

select [ProjectWork Number], WeekValue, 'Apr-03-2016', 'Apr-10-2016'

From PivotData

PIVOT

(

Sum(WeekValue) For WeekNO in...

I've created a dynamic sql for this, here is the generated dynamic query @sql

WITH PivotData AS

(

select [ProjectWork Number], WeekNO, WeekValue From dbo.staging

)

select [ProjectWork Number], WeekValue, Apr-03-2016, Apr-10-2016, Apr-17-2016,...

Thanks.

I hope this will do now:

CREATE TABLE [dbo].[Staging](

[ProjectWork Number] [nvarchar](255) NULL,

[WeekNo] [nvarchar](12) NULL,

[WeekValue] [float] NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[Staging] VALUES('12345', 'Dec 06, 2015', 12.3)

INSERT INTO [dbo].[Staging] VALUES('12345', 'Dec 13, 2015',...

Y.B. (11/24/2016)

---

You can do this using Dynamic SQL with a Crosstab. If you'd be willing to provide consumable data I could give you an example on how to do...

OK, this is what I am going to do: I will create a table with:

CREATE TABLE [dbo].[WeekTable](

[ID] [int] IDENTITY(1,1) NOT NULL,

[QuaterNo] [varchar](7) NOT NULL,

[WeekNo] [varchar](11) NOT NULL

) ON [PRIMARY]

And I...

Now I have imported 4 original sheets into 4 tables, with structure as below (Q1 as an example):

CREATE TABLE [dbo].[Q1 2016](

[ProjectWork Number] [nvarchar](255) NULL,

[ProjectWork Name] [nvarchar](255) NULL,

[WBS07 Lvl 1] [nvarchar](255)...

I am thinking the final table be designed like this:

key columns:

ProjectWork Number

ProjectWork Name

non-key columns:

Employee Number

Resource Type

Location

Resource Role

Shore Type

Preferred Vendor

Value columns:

QuarterNo

WeekNo

WeekValue

Does that make sense? is there a better design? Thanks...

Thank you Jo for the workaround.

Do you know if there is a way to save the package to run later? I am thinking if the package can be saved as...

Thank you but are you pasting the right link?...

Jeff Moden (11/12/2016)

---

halifaxdal (11/11/2016)

---

This is really a challenging to me: I am assigned folders (yes, plural, more than one folder) on SFTP server, each folder correspondent to one client. Each...

Jeff Moden (11/8/2016)

---

ZZartin (11/8/2016)

---

Yes it will process text files in line order, that is no guarantee however that they'll come out of a database table in that order. The...

Alan.B (11/8/2016)

---

You can accomplish this using NGrams2B which can be downloaded from here[/url] at the end of the article.). I don't know how you are bringing in your CSV file...

Thanks for all the inputs, can anyone recommend a good ETL tool to perform tasks like this?

Just to double check using Luis suggestion, I did it again from scratch and it works now, I deleted all the previous tables (generated but with 0 rows), from the...

I tried it again and increased the column size in Choose a Data Source->Advanced, I got the following errors:

- Validating (Error)

Messages

Error 0xc02020f4: Data Flow Task 1: The column "Company" cannot...