Si t'as pas envie d'utiliser un Cursor, regarde le SQL en dessous.

Faudrait l'adapter!

1) Faut creer la table Tally (merci Jeff Moden) directement dans la base et pas a chaque fois...

Pour executer une proc stocké avec la tache 'Execute Sql' tu entre comme SQL exec spMyStoredProcedure. Apres si tu as des parametres, ca change un peu. Mais essaie...

As-tu esssayé de mettre le sql dans une procedure stockée et ensuite appeler le proc stocké a partir de SSIS?

Superb!

Thanks very much!

jais.ghan,

Thanks but that doesn't work with a numeric or decimal sql datatype.

toolman,

Thanks but I've already tried all of these, and they all have a fixed precision e.g. I can't have...

The number of digits after the decimal depends on the data itself i.e. is not known in advance.

Thus 1.234560000 -> 1.23456

while 2.340000000 -> 2.34

Story of my life....

...I've discovered a new feature, just as it's being deprecated!

The problem is with your CTE syntax when used with INSERT.

Without rewriting your SQL, it should be basically in the following order (pseudo code.)

;WITH MissingDatesCTE (MissingDateField) AS (SELECT......)

INSERT INTO #Missing_Files(MissingDate)

SELECT...

You're right...my comments are quite severe, and probably over the top. So I apologise if I've offended.

But I do think that in an article the presentation is as important...

I certainly wouldn't call this an article. I found the "aside" most amusing - and is almost as long as the so called article.

If you are hesitating about...

Congratulations...both awards are very well deserved!

You should get a certain amount of compression if you use a typed xml column i.e. define a schema for your xml.

It won't be very compressed, but better than nothing....

...or in the case statement

CASE WHEN p.Name is null then 'N/A' ELSE p.Name END

(but isnull is better here.)

The issue is that nothing is equal to null i.e. p.Name =...

Looks like your database server is case sensitive (like mine.)

change SYSNAME to sysname and you should be ok.

Also sp_msforeachdb will have to become sp_MSforeachdb

Regards,

David.

I include an example script here. This is the change script generated when adding NewField between Field1 and Field2 of MyTable. I hope this makes it clearer.

My question...