How to Store System Variables in DB?

Hi,

I want to store the System::StartTime in my Integration Table Log. Please guide how can I use this in my Execute SQL Task Block. I write this SQL Statement:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?) Which the ? is for the parameter. I go to parameter mapping tab and map the System::SatrTime to parameter0, but the package fails to run. I also changed the ? to @.IntDate and also changed the parameter name but the error was the same.

Please help how to write this System Variable value in to that table.

Regards,
Samy

In this situation I recommend using expressions rather than parameters. This article talks about expressions to store a SQL statement for an OLE DB Source component but this principle can apply to Execute SQL Task as well: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

I know Jamie is a fan of using expressions over parameters, but I disagree, and there are situations where parameters are still better. Security being the big issue with expressions.

Anyway, to get your parameter to work, make sure you have set mappend the parameter on the "Parameter Mapping" page of the Execute SQL Task. Select variable StartTime, direction is Input, Data Type is DATE, and Parameter name is 0.

For OLE-DB connections the parameter name is a zero based incrementing count of the parameters.

The Data Type is DATE, which seems rather confusing, as normally DBTIMESTAMP is the SSIS data type to use, however these are clearly not normal SSIS data types. We just do not have enough different types within SSIS!

|||

Dear Darren,

I did what you mentioned, but the package is not functioning:

SSIS package "HRM_tblParty.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at HRM_tblParty: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "HRM_tblParty.dtsx" finished: Failure.

This is my Query:

INSERT INTO CMN_tblIntegration VALUES ('HRM_tblParty', ?)

and I mapped the System::StartTime to Parameter0 (Type: Date and Direction: Input)

Pleae help,
Sassan

|||

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

|||

SamyLahur wrote:

Dear Jamie,

Nice Solution with Nice blog.
But How do I call the other System Value in my SQL Variable. For example:

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.StartTime

or

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= " + @.System::StartTime

Both fails at defining the Data Flow Task.

Thanks.
Samy

The OLE DB Source component can be set by selecting a table, entering a SQL statement, or taking a SQL statement that is stored in a variable. You need to select the 3rd of these options.

-Jamie

|||

The parameter name should be 0, nothing else just the character for zero.

For the expression option you need to specify the variable name correctly-

@.[System::StartTime]

You will also need to convert to date time valeu to a string to do the concatenation-

"SELECT * FROM CMN_tblParty WHERE CreationDateTime <= '" + (DT_WSTR, 20 )@.[System::StartTime] + "'"

Note the quick and dirty conversion I have used is open to date format misinterpretation issues DMY, MDY etc. You should break it down and convert to component parts into a string as an unambiguous format. For SQL Server yyyymmdd is unambiguous. Of course using parameter support avoids this issue as it is treated as a date type all the way through.

|||

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

|||Have you read my last post? I appreciate that you may well have missed it due to the exceptionally well designed interface and threading capabilities of these forums.|||

SamyLahur wrote:

I know I must choose the SQL Command by Variable, but when I choose the variable, which has the above expression it fails to show the Value and says:

The expression for variable "NewItemsSQL" failed evaluation. There was an error in the expression."

What is the problem with expression?

Samy

My first guess would be that you need to cast the date as a string.

There is no expression editor on variable expressions which is (and I'm being polite here) very very BAD. It will appear in SP1 but in the meantime here's a tip for you. Build your expression against the "Description" property of the package. This will enable you to use the expression editor to build your expression and validate it within there. When you have it working, copy and paste the expression into the appropriate place for the variable.

Hope that makes sense.

-Jamie