SSIS avoid changing derived column expression manually

Question

I have an SSIS package which needs to run every first day of the week, source is from excel file and the destination is SQL table.

The current process is to replace the null value to 0 of excel file manually, (it takes a lot of time), so I have created a package and currently working with derived column expression "(Column1 == "" ? 0 : Column1)", but the excel file columns are not always the same to the derived column expression so every time I have to run the package I also have to edit the derived column expression. I'm looking for a better way to do this, and to avoid doing it manually. Thanks in advance.

If I understand correctly, one week the derived column may be based on Column1 and the next week it could be Column2, Column3 or another column? If this is the case, how do you know what column to apply from one week to the next? What is unique to this column relative to the others in the source Excel worksheet? — user3662215, Oct 15 '15 at 19:48
I have edited my comment. The excel columns contain of last week dates, so Column1 is the date for Monday(20151005), Column2(20151006), and so on... Then in the destination table the Columns are Monday, Tuesday, until friday — r-r, Oct 15 '15 at 19:57
Sorry, I'm not seeing any additional information. Also, are you having to open the derived column task manually each week to change the column source? — user3662215, Oct 15 '15 at 20:00
The worksheet contains columns for each workday of the week (Monday through Friday) with Column1 = Monday, Column2 = Tuesday, Column3 = Wednesday, Column4 = Thursday, and Column 5 = Friday. Each column contains the date for that day of the previous week. Given this, what is the rule that you are applying to determine which of the five to use for a derived column? — user3662215, Oct 15 '15 at 20:10
yes that's the current scenario for now, everytime I have to change the source I also have to change the derive column expression — r-r, Oct 15 '15 at 20:17
I understand the Excel source now. But why are you having to change the derived column source in the expression each week to point to a different column? — user3662215, Oct 15 '15 at 20:33
Here's the sample derived column expression [ 20151005 ] == "" ? 0 : [ 20151005 ] The column names are dates not the datenames, so that's why I have to change it manually — r-r, Oct 15 '15 at 20:39
How flexible is your Excel source? You could go with static column name (Monday, Tuesday, etc) and then derived your actual dates. That way you would have something like `[Monday] == "" ? 0 : [Monday]` — sorrell, Oct 15 '15 at 20:48
Okay, now I understand. Because the date for, let's say Monday, changes from one week to the next, the column header also changes. For example, the header for the first column last week was 20151005 and the week before it was 20150928. The reason this caught my attention is because I recently created a SSIS package that dynamically reads in many Excel files, containing one or more worksheets with each worksheet containing different column headers. Each worksheet then became its own database table. Anyway, I think I can help and it will not be too complex. — user3662215, Oct 15 '15 at 20:55
Skip the column headers an option? Column1 is always Monday regardless of the value in row 1? — billinkc, Oct 15 '15 at 20:58
Can the person who creates the Excel file each week, just name the first column "Monday" instead of for example "20151005" or is that something you have no control over? — user3662215, Oct 15 '15 at 21:05

score 1 · Answer 1 · answered Oct 15 '15 at 20:48

There's no easy built-in way to handle dynamic column names in SSIS at all, let alone worrying about the expression in a derived column transformation.

Use BiML to dynamically create SSIS packages that will import the file based on its current column names.

Google BiML Tutorial to start learning BiML, and good luck.

user3662215 · Answer 2 · 2015-10-15T22:38:02.907

If it is not possible to have the person who creates the Excel file each week to have them name the five columns "Monday", "Tuesday", "Wednesday", "Thursday", and "Friday", instead of "20151005", "20151006", "20151007", "20151008", and "20151009" (for example that which was applied for last week), then this can be done with the following steps.

Steps:

Create a new global user variable of type string in your SSIS package and call it something like "Day."
In your derived column expression replace "20151005" for example with the new user variable. You can drag it down from above and it will look something like @[user::Day].
Now to dynamically populate that new Variable "Day", add a SQL script that runs the following SQL with a ResultSet of a single row. To the Result Set in the left add your user variable "Day" under the Variable Name column. Give the Result Name a value of "0". Under General, set the Source Type to Direct Input and provide the following SQL that will return the date value of Monday of the previous week in the yyyymmdd format.
```
declare @date as datetime 
set @date = dateadd(week, datediff(week, 0, getdate()-7), 0) 
select cast(year(@date) as char(4)) + right('00' + convert(varchar(2), month(@date)), 2) + right('00' + convert(varchar(2), day(@date)), 2)
```

The key here is that you can run the SSIS package any day of the following week and it will capture this information for the Monday of the prior week. But if you skip a week, it will be looking for a column that no longer exists. The alternative to applying a SQL script to capture the date of the previous week's date for Monday is to read the first column from the worksheet directly and this becomes complex, because it involves using a script task using Visual Basic or C# instead... requiring Excel references. Much more involved but can be done.

Hope this helps.

The SQL script that populates the variable just needs to be placed anywhere before the step where the same variable is applied in the derived column. — user3662215, Oct 23 '15 at 22:38

SSIS avoid changing derived column expression manually

2 Answers2