more expression syntax

Hi,

This is a follow up to an earlier question. I'm having a heck of a time here. What I'm doing is reading a value into a SSIS variable and trying to evaluate it. I know the value is huge (over 4000 chars) and what I think should happen in my package isn't (I guess because this variable is so big).

What I WANTED to do is a straight character check:

@.[User::xml_output] == "ABC"

but that wasn't working... so I deceided to try the len function.

However xml_output is too big and it's also not working. How would I check to see if the len is greater than 17 characters? Here is what I have so far...and none of it works.

len(trim(DT_WSTR,18,1252)@.[User::xml_output])) > 17

len(trim(@.[User::xml_output])) > 17

(DT_WSTR,18)@.[User::xml_output] > 17

I just want to trim @.[User::xml_output] and see if it's greater than 17 characters. Any help would be appreciated.

Thanks,

Phil

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

I don't know if you're trying to do this in a data flow or a control flow, but here is the code for a control flow script task that gets the length of your variable and puts it into an integer variable named "length". You would of course need to list "xml_output" and "length" in the ReadOnlyVariables and ReadWriteVariables properties respectively.

Code Snippet

Public Sub Main()
Dts.Variables("length").Value = Dts.Variables("xml_output").Value.ToString.Length
Dts.TaskResult = Dts.Results.Success
End Sub

|||Thanks.|||

JayH wrote:

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

If you're referring to me, well, I'm always in favor of using the right tool for the job. That includes being efficient. This solutions works perfectly because his string is over 4,000 characters.|||

I figured you guys worked together or something

It's actually a huge shortcoming of expression syntax to not be able to include over 4000 characters. I was trying to evaluate some XML output and it was over the 4k limit and it basically always assumed it was under 17 characters because that's what my expression syntax was looking for either greater than or less than 17 characters. The less than would always hit because it just ignored the large size of the variable. Work-arounds are good, but in this case I think the language needs to adapt....

Phil

sql

Month and day of imported datetimes are switched

Hello!

I′m not quite sure if this is the correct forum for this question but anyway:

I`m importing columns from an IBM Informix Server (7.31) with SSIS to SQL Server 2005 SP1. My problem is that in all datetime values where the day is <=12, month and day are switched, which of course gives me a false date. The date is imported as month/day/year.

Example:

real date: 01/24/2006 date in SQL Server 2005: 01/24/2006

real date: 01/02/2006 date in SQL Server 2005: 02/01/2006

I read that SQL Server always stores dates in the same way and only the output type is specified by the clients′ regional and language settings. But the dates are definitely stored false.

Any suggestions?

I believe that SQL Server's default interpretation of ambigious string dates is YMD.

You may need to use the DateFormat command prior to your data import in order to remove any ambiguities.

SET DATEFORMAT mdy

|||Hmm. The problem somehow dissappeared. I did the suggested DATEFORMAT and also changed the default Short Date Format to MM-DD-YYYY.

Monitoring the running and execution of SSIS packages with MOM

Hi,

Has anyone monitored the execution of SSIS packages with MOM? Are there extreme benefits over just utilizing the built in execution and event logs, as well as the Windows Event Viewer?

What is the recommended way to monitor SSIS execution?

Thanks,

- Joel

Joel,

I have a very very limited knwoledge of MOM... However you can log many things to the event log with SSIS (job execution with SQL Agent, logging to event log, ...). I'm not aware of anything "standard" for SSIS & MOM...

|||

They're not quite there yet with MOM integration. We had a similar project to integrate into Tivoli and the way I got around it was to use logging providers to output into the Windows Event Log. Tivoli had hooks into the Event Log for the events I gave it and whalla....pagers blaring. If your concern is more from the operations perpective, you may have better luck using the SQL Server log provider and creating operations reports for your production support. You can use these and events like the OnPipelineRowsSent to trap "hung" packages or tasks.

Sorry there's not a better answer but direct MOM integration is just a matter of time.

-- Brian

sql

Monitoring a folder for a file - start ssis

Hi

I want my package to monitor a directory for a file and when it detects one it will start processing the file. Any help would be appreciated

Regards

Ants

The only (and easy) idea that comes to my mind is to schedule the package to run n times a day and to include conditional logic in control flow based in the existence of files on the folder.

Rafael Salas

|||

You can use the WMI task to do this. There is a sample in BOL.

Or, you can download the fileWatcher Component. http://www.sqlis.com/default.aspx?23

|||WMI is a good option here and the WMI Watcher Task will help you to achieve the same.

I remember reading somewhere that the memory used by the package will

be released only when the package is stopped, so in your case if the

package is going to be waiting for the file (package in run state), we

need to consider the side effects of memory.

Thanks,

S Suresh

Monitor SQL Agent job in ASP.Net

Hi all,

I have a web page which calls a SQL Agent job to initiate an SSIS package. Is there any way i can monitor the job and detect once it has completed successfully? At present all i can seem to get is the return status of whether the job has started or not.

Any help would be most appreciated,

Many thanks in advance.

Grant

You could query the sysjobhistory table. When steps complete, rows get written to this table. You could probably access this via SMO as well, I haven't checked. There is an instance_id which will allow you to track your specific instance, assuming you get that as a token when starting. It may depend on how you are doing this, T-SQL or SMO, I haven't looked vbery far into this, but start with sysjobhistory.

moniker for own SSIS task

How can i find out the moniker for my own ssis task?

(Assuming you are using C#, VB.NET or other .NET language)

The moniker is assembly-qualified type name of your task class, same as here

http://msdn2.microsoft.com/en-us/library/system.type.assemblyqualifiedname.aspx

This article might also help:

http://msdn2.microsoft.com/en-us/library/2exyydhb.aspx

|||thanks