SSIS Insert Data After Aggregate (Group By) with new Unique key

1

I have the Contoso Dataset of Microsoft. My goal is from this dataset which is a flat file, to fill tables on my database. More specific the flat file contains three columns like these:

  1. CurrencyLabel
  2. CurrencyName
  3. CurrencyDescription

My goal is to remove duplicates and insert the unique results in my table that have these columns 1. CurrencyKey 2. CurrencyLabel 3. CurrencyName 4. CurrencyDescription

The CurrencyKey column is the primary key of the table and it should be filled automatically with new keys of the results on SSIS.

At SSIS, i have made a control flow which contains the data flow.

In the data flow I have the flat file source, then aggregate with group by with all columnns (CurrencyLabel, CurrencyName,CurrencyDescription ) and then I Have set the ole db destination to my table.

The goal is to fill all my four columns and the target is to increase the CurrencyKey from the aggregate.

The columns of the flat files and the first two rows:

OnlineSalesKey|SalesQuantity|SalesAmount|TotalCost|UnitCost|UnitPrice|CustomerLabel|Title|FirstName|MiddleName|LastName|BirthDate|MaritalStatus|Gender|EmailAddress|YearlyIncome|TotalChildren|NumberChildrenAtHome|Education|Occupation|HouseOwnerFlag|NumberCarsOwned|Phone|CurrencyLabel|CurrencyName|CurrencyDescription|IsWorkDay|IsHoliday|HolidayName|EuropeSeason|NorthAmericaSeason|AsiaSeason|ProductLabel|ProductName|ProductDescription|Manufacturer|BrandName|ColorName|Size|Weight|ProductCategoryLabel|ProductCategoryName|ProductCategoryDescription|StoreManager|StoreType|StoreName|StoreDescription|Status|OpenDate|CloseDate|StorePhone|StoreFax|Customer_address|store_address
19609697|1|313.52|180.22|180.22|391.9|18157||Isabella||Hall|1953-03-06|S|F|isabella80@adventure-works.com|90000|3|2|Partial College|Professional|1|1|1 (11) 500 555-0181|001|USD|US Dollar|WorkDay|0|None|Holiday|Spring/Back to Business|Holiday|0402095|Fabrikam SLR Camera M150 Orange|Digital camera ñ SLR, 5.2 in x 2.8 in x 3.7 in, 19.2 oz|Fabrikam, Inc.|Fabrikam|Orange|3.5 x 5 x 3.3 |5.25|04|Cameras and camcorders |Cameras and camcorders |246|Online|Contoso Europe Online Store|Contoso Europe Online Store|On|2004-09-03 00:00:00||731-555-0117|731-555-0117|Bundesallee 4422|Downtown Berlin, Germany
19609733|1|166.4|95.65|95.65|208|14866||Hailey|R|Peterson|1970-10-21|S|F|hailey14@adventure-works.com|40000|2|2|Partial College|Clerical|1|2|1 (11) 500 555-0123|001|USD|US Dollar|WorkDay|0|None|Holiday|Spring/Back to Business|Holiday|0504005|The Phone Company Smart phones 160x160 M26 Black|Smart phones 160x160, AC adapter, stylus, protective cover, installation CD-ROM, application manual|The Phone Company|The Phone Company|Black|4.5 x 3.1 x 0.6|4.5999999999999996|05|Cell phones|Cell phones|246|Online|Contoso Europe Online Store|Contoso Europe Online Store|On|2004-09-03 00:00:00||731-555-0117|731-555-0117|Pflugstr 24|Downtown Berlin, Germany

You can see that the fields are 001|USD|US Dollar 1. CurrencyLabel 2. CurrencyName 3. CurrencyDescription

My table

table

My data flow

dataflow

Aggregate by group by

aggregate

Mappings on OLE DB DESTINATION

mappings

Right now nothing seems to be written in my table and there is error by this this output:

SSIS package 
Information: 0x4004300A at Fill Currency Table, SSIS.Pipeline: 
Validation phase is beginning.
Information: 0x4004300A at Fill Currency Table, SSIS.Pipeline: 
Validation phase is beginning.
Warning: 0x80049304 at Fill Currency Table, SSIS.Pipeline: Warning: 
Could not open global shared memory to communicate with performance 
DLL; data flow performance counters are not available.  To resolve, run 
this package as an administrator, or on the system's console.
Information: 0x40043006 at Fill Currency Table, SSIS.Pipeline: Prepare 
for Execute phase is beginning.
Information: 0x40043007 at Fill Currency Table, SSIS.Pipeline: Pre- 
Execute phase is beginning.
Information: 0x402090DC at Fill Currency Table, Flat File Source [30]: 
The processing of file "C:\Users\george\Desktop\Data_201813_10M.txt" 
has started.
Information: 0x4004300C at Fill Currency Table, SSIS.Pipeline: Execute 
phase is beginning.
Information: 0x402090DE at Fill Currency Table, Flat File Source [30]: 
The total number of data rows processed for file 
"C:\Users\george\Desktop\Data_201813_10M.txt" is 10000001.
Error: 0xC0202009 at Fill Currency Table, OLE DB Destination [102]: 
SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error 
code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native 
Client 11.0"  Hresult: 0x80004005  Description: "The statement has been 
terminated.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native 
Client 11.0"  Hresult: 0x80004005  Description: "Cannot insert the 
value NULL into column 'CurrencyKey', table 
'DataWarehouse.dbo.DimCurrency'; column does not allow nulls. INSERT 
fails.".
Error: 0xC0209029 at Fill Currency Table, OLE DB Destination [102]: 
SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "OLE DB 
Destination.Inputs[OLE DB Destination Input]" failed because error code 
0xC020907B occurred, and the error row disposition on "OLE DB 
Destination.Inputs[OLE DB Destination Input]" specifies failure on 
error. An error occurred on the specified object of the specified 
component.  There may be error messages posted before this with more 
information about the failure.
Error: 0xC0047022 at Fill Currency Table, SSIS.Pipeline: SSIS Error 
Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component 
"OLE DB Destination" (102) failed with error code 0xC0209029 while 
 processing input "OLE DB Destination Input" (115). The identified 
component returned an error from the ProcessInput method. The error is 
specific to the component, but the error is fatal and will cause the 
Data Flow task to stop running.  There may be error messages posted 
before this with more information about the failure.
Information: 0x40043008 at Fill Currency Table, SSIS.Pipeline: Post 
Execute phase is beginning.
Information: 0x402090DD at Fill Currency Table, Flat File Source [30]: 
The processing of file "C:\Users\george\Desktop\Data_201813_10M.txt" 
has ended.
Information: 0x4004300B at Fill Currency Table, SSIS.Pipeline: "OLE DB 
Destination" wrote 1 rows.
Information: 0x40043009 at Fill Currency Table, SSIS.Pipeline: Cleanup 
phase is beginning.
Task failed: Fill Currency Table
Warning: 0x80019002 at DataWarehouse2018: SSIS Warning Code 
DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but 
the number of errors raised (3) reached the maximum allowed (1); 
resulting in failure. This occurs when the number of errors reaches the 
number specified in MaximumErrorCount. Change the MaximumErrorCount or 
fix the errors.
SSIS package 

Any idea?

Thank you

sql-server
ssis
group-by
aggregate
asked on Stack Overflow Dec 2, 2018 by giorgionasis • edited Dec 2, 2018 by giorgionasis

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0