CODEWITHSUNDEEP
excelssisssis-2008
Ruzel R.
Ruzel R.

Reputation: 327

SSIS avoid changing derived column expression manually

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.

Upvotes: 0

Views: 493

Answers (2)

user3662215
user3662215

Reputation: 526

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.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 1

Related Questions