SSIS Package and Foreign Keys

1
votes

I have an SSIS Package that is importing a CSV file into a table called "OPEX_SPEND". I also have a table called OPEX_Accounts with the following columns:

  • opex_id (primary key)
  • opex_name
  • gl_account

The "OPEX_Spend" has the following columns:

  • OPEX_SpendID (primary key)
  • opex_id (foreign key to OPEX_Accounts table)
  • Date
  • ItemText
  • Amount

I've created a data flow where the CSV will import the values into the columns of the OPEX_Spend table but the foreign key column (opex_id) is coming up as "NULL". How do I fix this?

2
sqlsql-serverssisssmsetl
what is the expected value of opex_id? what is the structure of the csv file? please provide a sample of date? - Hadi
How is opex_id coming up null? Are you fetching this value with a lookup to the table OPEX_Spend?? Are these being loaded in the same dataflow? Could you please update the question with more details and perhaps an image of the dataflow or package? - Mark Wojciechowicz
Sorry for the late reply. There is no lookup in the data flow. Do you suggest that I incorporate it into the flow? - Max

2 Answers

0
votes

If really missing value in CSV file, to create a dummy Opex_Id, for example

INSERT INTO Opex_Accounts
(Opex_Id 
, Opex_Name
, Gl_Account
)
VALUES(-1
  ,'UNKNOWN'
  ,'UNKNOWN'
);

ALTER TABLE Opex_Spend ADD CONSTRAINT [DF_Opex_Spend_Opex_Id]  DEFAULT (-1) FOR Opex_Id;
0
votes

if you do not have a source column for the opex_id in the CSV file, then one thing you can do is to add a dummy default value for all records. For that, you can use a Derived column transformation in SSIS. But if you mean that you have values in the source for that column but you want to have some default value in the NULL source values, then also you can use the Derived column transformation But instead of choosing Add New column you can choose to replace an existing column and then use the REPACENULL function in SSIS to treat the NULL values as something else.