Sql server integration services

[nofu1]

Hi all;

I am still new to SSIS. I have an SSIS service that does an ETL process for me utilizing a flat file (csv). In sql server I created a table with the following columns below

create table testme
(
id int identity (1, 1)
,date_load datetime
,source_info varchar(10)
,desc_info varchar(20)
,desc_type varchar(20)
)

my csv file only contains columns desc_info and desc_type. My question though is, how do populate the date_load and source_info column in the SSIS package. Is there a way to add addition columns and map it. Thanks in advance.

 

[Skydiver]

My understanding of SSIS, is that you can insert custom code anywhere asking the pipeline. So to me, if the wizard that lets you pick the inputs and outputs does not let you set those values, then insert a module that says the values as the data moves through the pipeline.

As to how exactly that is done, I don't know, since in subscribe to the NoSQL philosophy.

 

[jmcilhinney]

I haven't used SSIS too much or for some time so my understanding is not complete. I'm fairly sure that there's a task for loading files but, as @Skydiver suggests, if that doesn't give you the flexibility you need, you should add a Script Task and then you can write code to do whatever you want. I'm not 100% sure whether C# is supported in Script Tasks as I always wrote mine in VB.

 

[nofu1]

So, I have figured some of it out. you will need to create an SSIS package with the following components

Flat file source --> script component --> OLE DB destination

In the script component, you will click edit to write a bit of C# or VB code in the following methods below. for instance, in the above example the code will be as follows below

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
    


Row.date_load = Datetime.Now;

Row.source_info = "Flat file source A";
Row.id = 1;


}

and then you can map it. Note, do not map the id column since it is an identity and can only be read-only in ssis, hence leave the mapping empty