Sql-server – SSIS Connection String Expression at Run Time and during Execution

sql-server-2008ssisvisual studio 2010

I have an Excel connection manager which should write to a file which does not exist prior to runtime but rather is created during execution. I have an expression set for the connection string property of the excel connection manager which points to the file that will be created. The problem I am having is that since the file does not yet exist (prior to execution), when I go to run the package it is giving me an error since it can not find the file. If i create a dummy file and place it where the excel connec manager is pointing the package will run ok.

What I have tried to do is point the excel connection manager to an existing file prior to run time since it will not give an error and then hopping it will then pick up the new Path (excel connection string) from the Expressions but what is happening is that since I have an expressions set for the connection string property it will not let me overwrite the connection string property, it keeps defaulting to the value of the expressions which contains other variable that are populated during run time so that is where I am getting my error. The expressions string prior to runtime is not pointing to an actual file because it does not yet exist but mainly because the expressions is not fully populated until runtime since it also contains variables.

As of right now it seems like I might have to just point to a dummy template file (without using any expressions) prior to runtime and then maybe use a Script to change the connection string property of the excel connec manager to = a variable containing an expression containing the path to the file….

Does this sound like the way to go or am I over complicating things?

Best Answer

When a package receives a signal to start, one of the tasks it performs is to ensure everything it needs is available and has the appropriate signatures. SSIS is all about the metadata. The idea behind this is that a package should blow up before it does any work if it can tell at the start that an error will occur (can't connect to a server, table doesn't exist, data type is incompatible, etc). It's generally a good thing, why start something you know you can't finish and potentially leave yourself in a messy/inconsistent state.

In your scenario, it is validating that this file is there and since it isn't, because it's generated at run-time, it fails. Therefore, you have to flag it such that the validation occurs right at the moment the resource is needed. As @mike Henderson indicated, you'll need to go into the properties of the objects and change the DelayValidation value from False to True. You will probably need to set it on both the Connection Manager and the Data Flow Task that uses it. Click once on the item, hit F4 or right click and select Properties.

Usage

In your source query (and you are using a source query and not simply selecting the table in the drop down), explicitly cast things to an appropriate n(var)char length.

Instead of

SELECT
    E.BusinessEntityID
,   E.NationalIDNumber
,   E.LoginID
,   E.JobTitle
,   E.BirthDate
,   E.MaritalStatus
,   E.Gender
FROM
    HumanResources.Employee AS E

use a query such as

SELECT
    CAST(E.BusinessEntityID AS nvarchar(10)) AS BusinessEntityID
,   CAST(E.NationalIDNumber AS nvarchar(15)) AS NationalIDNumber
,   CAST(E.LoginID AS nvarchar(256)) AS LoginID
,   CAST(E.JobTitle AS nvarchar(50)) AS JobTitle
,   CAST(E.BirthDate AS nvarchar(10)) AS BirthDate
,   CAST(E.MaritalStatus AS nchar(1)) AS MaritalStatus
,   CAST(E.Gender AS nchar(1)) AS Gender
FROM
    HumanResources.Employee AS E

The astute students of AdventureWorks will recognize the data was already an n(var)char type but this was merely to demonstrate the concept.

Benefits

Less memory used. Currently, you'd be allocating and storing two copies of the "same" data by using the Data Conversion Component in SSIS
No RSI. No need to click N times and specifying all that information in the awful little editor they provide. I'd use a query against the dmv/information_schema to further automate the generation of "Excel ready" table exports.
No custom component installation. I've worked at places where installing open source was verboten. There is also a deferred maintenance cost to installing third party apps as now "everyone" needs to install that same app to maintain your code and it needs to get installed on the servers and infosec needs to scrutinize the assemblies to make sure they're valid and we need signoffs from a bajillion people who outrank you...

Sql-server – Why is Excel File Opened Exclusively By Another

Ensure you have the right extension and path to the directory set in your variables within each object including the whereloop and connection file.

*.xls versus *.xls*

Best Answer

Related Solutions

Converting Non Unicode string to Unicode string SSIS

Usage

Benefits

Sql-server – Why is Excel File Opened Exclusively By Another

Related Question