SSIS avoid changing derived column expression manually

0
votes

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.

2
excelssisssis-2008
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
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
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
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
Skip the column headers an option? Column1 is always Monday regardless of the value in row 1? – billinkc

2 Answers

1
votes

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.

1
votes

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:

  1. Create a new global user variable of type string in your SSIS package and call it something like "Day."
  2. 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].

  3. 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.