> Thanks -- but how does this work when I need to combine fields into
> one field? I.E., I have a date field and another field for time in
> the CSV. I need to merge the two into a datetime field.
>
> I'm not sure the Pivot table allows me to do this.
>
> I was thinking I need a Script Task or something... since that is what
> I use today for DTS. I just can't figure out how to transform each
> field and insert a new row for each....
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:b067a62520738c952e4409f3ecd@news.microsoft.com...
>
>> Hello dm3281,
>>
>> OK So you essentially want to pivot the data right?
>>
>> Have a look here and see if it gives you any ideas
>>
>> http://wiki.sqlis.com/default.aspx/SQLISWiki/PivotAndUnpivotTransform
>> Resources.html
>>
>> --
>>
>> Allan Mitchell
>> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com
>> |
>> http://www.konesans.com
>>> Hello, I'm in the beginning phases of rewriting a simple DTS package
>>> under SQL 2000 to SSIS under SQL 2005.
>>>
>>> My simple package today does the following:
>>>
>>> 1) Pulls a file down to C:\TEMP via FTP
>>>
>>> 2) Contains 15 Transformation Tasks (Groups) where each task is
>>> reading a date, time, group, and DataX column. I have basically 15
>>> columns (one for each Data colums). The DTS task essentially reads
>>> in column and outputs a new row with the date & time concatentated,
>>> group, and dataX values.
>>>
>>> 3) Output is written to a SQL 2000 database.
>>>
>>> I'm having issues with how to accomplish this within SQL 2005. I
>>> can't quite figure out the tasks and data transformation objects
>>> that I need to accomplish what I want.
>>>
>>> The data input looks something like
>>>
>>> The input CSV file:
>>> ProcessDate, ProcessTime, Group, Data1, Data2, Data3, Data4, Data5
>>> 01/01/2007,01:00,Group1,100,200,300,400,500
>>> 01/02/2007,02:00,Group2,900,800,700,600,500
>>> 01/24/2007,09:45,Group3,10,20,30,40,50
>>> The transformation Group1 outputs rows as:
>>> 01/01/2007 01:00, Group1, 100
>>> 01/01/2007 01:00, Group1, 200
>>> 01/01/2007 01:00, Group1, 300
>>> 01/01/2007 01:00, Group1, 400
>>> 01/01/2007 01:00, Group1, 500
>>> The transformation Group2 outputs rows as:
>>> 01/02/2007 02:00,Group2, 900
>>> 01/02/2007 02:00,Group2, 800
>>> 01/02/2007 02:00,Group2, 700
>>> 01/02/2007 02:00,Group2, 600
>>> 01/02/2007 02:00,Group2, 500
>>> The transformation Group3 outputs rows as:
>>> 01/24/2007 09:45,Group3,10
>>> 01/24/2007 09:45,Group3,20
>>> 01/24/2007 09:45,Group3,30
>>> 01/24/2007 09:45,Group3,40
>>> 01/24/2007 09:45,Group3,50
>>> The above illustrates that I'm reading a row from input, and
>>> outputing
>>> 5 rows to destination. I'm concatentating the date and time columns
>>> into one datetime column, the group column contains the group name,
>>> and then the data column contains the data value from the specified
>>> column in input file.
>>> How do I accomplish this in SSIS?
>>>
>>> Any help or examples would be appreciated!
>>>