Setting a Flag - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Setting a Flag

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-09-19 : 04:37:40

Hi Guys

I'm stuck the following problem.

I have a SSIS package that imports two files (File1 and File 2) into one SQL table. I need to set a flag within the SQL table that the data is being imported in. So if the data is from File1 it places a 1 in the Flag field and if it is from File2 it places a 2 in the Flag field.

Is this possible to do?

Thanking you in advance!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-09-19 : 09:45:00

Sure. Just use sql statement like this;

For first transformation,

Select fld1, fld2, ...., 1 from soure_table1

And the next

Select fld1, fld2, ...., 2 from soure_table2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 09:50:24

Declare a variable to hold filename.
Add a derived column task to add a column to check value of file and set its value to 1 or 2. then include that new column in final OLEDB destination step to get value in table.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-09-19 : 10:54:52

Hi

Thanks Visakh for that, but I not totally clear on how you explaining it.

Sorry I've never used the Derived Column task before. Could you explain it again?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-19 : 13:08:54

quote:
Originally posted by rcr69er

Hi

Thanks Visakh for that, but I not totally clear on how you explaining it.

Sorry I've never used the Derived Column task before. Could you explain it again?

Thanks

how are you exporting two files currently? Are you using seperate data flow tasks?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-09-20 : 12:49:47

Hi

Yes I am currently using separate data flows for them.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 02:37:09

quote:
Originally posted by rcr69er

Hi

Yes I am currently using separate data flows for them.

Thanks

then its much easier. just include a derived column task in data flow between source and destination tasks and give new flag column name(let it be File_Flag) and value as 1 in first and 2 in second data flow task. also rememeber to select option add as a new column. then in destination task map this column to your flag column in destination table.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-09-21 : 09:31:16

Hey Thanks for that!!!

One thing, how do you assign the values. Do you do it in the derived column task or in the output destination part? Sorry I'm still a beginner with SSIS.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 13:07:18

quote:
Originally posted by rcr69er

Hey Thanks for that!!!

One thing, how do you assign the values. Do you do it in the derived column task or in the output destination part? Sorry I'm still a beginner with SSIS.

Thanks

the assigning of value to newly created column is done in derived column task. once this is done this new column will be available in metadata of pipeline thereafter (green line). Then in destination task you assign this new column to column of your destination table which you created for storing flag value.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-09-22 : 18:26:52

Hi

Thanks for that visakh, helped me out a lot!

Now I have another scenario. Say that I have 3 files. One being a Full Update, the second being a Partial Update, and the third being Records to be Deleted. The Full Update is run once a month, and every other time the last two files are being read. At the moment my package has all three files being read. Is it possible for the package to run data flow tasks for the 2nd and 3rd files if the first one doesn't exist. At present all three files are kept in the same location. So at any one time in the location will contain the Full Update file by itself, or the Partial Update and Records to be Deleted file.

I hope this makes sense!

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 01:08:29

quote:
Originally posted by rcr69er

Hi

Thanks for that visakh, helped me out a lot!

Now I have another scenario. Say that I have 3 files. One being a Full Update, the second being a Partial Update, and the third being Records to be Deleted. The Full Update is run once a month, and every other time the last two files are being read. At the moment my package has all three files being read. Is it possible for the package to run data flow tasks for the 2nd and 3rd files if the first one doesn't exist. At present all three files are kept in the same location. So at any one time in the location will contain the Full Update file by itself, or the Partial Update and Records to be Deleted file.

I hope this makes sense!

Thanks

You can make the SSIS package read only files available in a location. What you need to do is add a ForEachLoop container and use ForEachLoopFile enumerator. Give the location where file is found and it will only process files that are found inside the location. Inisde the loop you can include tasks to process the file data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources