THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

SSIS Design Pattern - ETL Instrumentation, Part 1

This blog has moved! You can find this content at the following new location:

http://andyleonard.blog/2007/08/03/ssis-design-pattern-etl-instrumentation-part-1/

Published Friday, August 3, 2007 4:36 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Davide Mauri said:

Hi Andy

you did a great work, but I have to say that for me instrumentation should me something "above" packages and not IN the package. In this way you can turn instrumentation on or off whenever you need.

I've developed a tool that allows you to do this:

http://www.codeplex.com/DTLoggedExec

you just need to create a plugin that write to database instead writing in a log file as it actually do.

Otherwise you can just wait a couple of days so that I can release the new versions that uses Log4Net, so that you can just configure it to write on SQL Server. :-)

August 3, 2007 8:25 AM
 

andyleonard said:

Hi Manowar,

  Thanks!

  I clicked the link and read through your page on DTLoggedExec. That looks really cool. Very impressive work!

  There are several reasons I like to keep the instrumentation inside the SSIS package process. My reasons will become more clear (I hope) as the series progresses. One reason is I like to use out-of-the-box transformations. This is clearly a preference on my part, but it has implications for application management lifecycle.

  SSIS is so flexible that good people can disagree on which is better; something in-line or something external. Personally, I believe everyone should use the approach with which they're most comfortable.

:{> Andy

August 8, 2007 7:09 PM
 

Joe Salvatore said:

Sorry to jump is so late.

I could not resist posting since I know both Andy and Davide (aka Manowar).

Would you also be able to intercept failures/errors, update the packageEndDateTime while also enter a value like "Failed" for the packageStatus?

What are some good ways of accomplishing this? ExecuteSQL in the OnError Event Handler perhaps?

November 9, 2007 8:53 PM
 

andyleonard said:

Hi Joe,

  Part 2 of the series addresses that... I need to get it posted!

:{> Andy

November 10, 2007 4:57 AM
 

Andy Leonard said:

Introduction This post is part of a series of posts on ETL Instrumentation. In Part 1 we built a database

November 11, 2007 1:21 AM
 

andyleonard said:

November 11, 2007 11:59 AM
 

Andy Leonard said:

Introduction This post is part of a series of posts on ETL Instrumentation. In Part 1 we built a database

November 18, 2007 10:53 PM
 

T Mark Fink said:

sVersion = Dts.Variables(sVersion).Value.ToString

  at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

  at ScriptTask_1e047fb858904a059a7fbeedd6f98d42.ScriptMain.Main()

Andy  I am getting these errors. I think I tracked it down to this line. What am I doing wrong?

Mark

January 4, 2008 1:48 PM
 

andyleonard said:

Hi Mark,

 In the sVersion assignment, the line should read:

sVersion = Dts.Variables("sVersion").Value.ToString.

  Your line is missing the double-quotes surrounding the Variable name.

  Does that fix it?

Hope this helps,

Andy

January 4, 2008 3:29 PM
 

Mike B said:

Hi Andy, great article, however, when I run your example I get the following error:

[Execute SQL Task] Error: Executing the query "Exec ssis.usp_RecordPackageStart ?,?,?,'Started'" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any ideas? Thanks

Mike

January 21, 2008 10:50 AM
 

andyleonard said:

Hi Mike,

  It sounds like either you're missing one of the parameters in the "Record Start of Package Execution" Execute SQL Task. Check that page in the editor and make sure you have three parameters listed (System::StartTime, System::PackageName, and User::sVersion) and that they're all configured as INPUT parameters.

Hope this helps,

Andy

January 21, 2008 11:08 AM
 

Mike B said:

Andy, please ignore my question. I figured it out. Thanks.

Mike B

January 21, 2008 12:40 PM
 

andyleonard said:

Good Job Mike!

:{> Andy

January 21, 2008 2:26 PM
 

Rhea said:

Hi Andy,

Good Post....

I tried the whole thing but i am getting error message in 'Log End of Package Execution'. Here is the message text

[Execute SQL Task] Error: Executing the query "declare @Now datetime set @Now = GetDate()   Exec ssis.usp_RecordPackageEnd ?,@Now,'Succeeded'  " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please respond

January 24, 2008 8:17 AM
 

andyleonard said:

Hi Rhea,

  That's a tough call without being able to see how you configured the Execute SQL Task. When I get that error it's usually something I did with the Parameter configuration. I would double-check to make sure you named the Parameter "0", set the direction to "Input", set the data type to "Long", and the variable to "User::iPackageLoadID".

Hope this helps,

Andy

January 24, 2008 8:47 AM
 

Rhea said:

everything is same as you had mentioned. Still the error appears......

January 24, 2008 10:22 AM
 

andyleonard said:

Hi Rhea,

  Please zip up the package and email it to me: andy dot leonard at gmail dot com.

:{> Andy

January 24, 2008 3:54 PM
 

Frank said:

Andy,

I am having the same issue as Mike B with the "Record Start of Package Execution" Execute SQL Task.

I tried everything posted in these comments, to no avail...What did Mike B do to solve the issue ?

thanks in advance, and thanks for the column....

Frank

January 29, 2008 11:30 PM
 

andyleonard said:

Hi Frank,

  I am not sure what Mike did to resolve the issue he encountered. If you're stumped and can hold out until I get some time to look at it, zip up the package and email it to me at andy dot leonard at gmail dot com.

:{> Andy

January 30, 2008 7:39 AM
 

andrewyorkbr said:

Hi Andy,

I'm working through your great article on SSIS Design Pattern - ETL Instrumentation, Part 1.

I built the package etc as i went along, all worked great until i added the last execute sql task to log the end of package execution. I'm getting the same error message as Rhea who posted on Jan 24th. The error is:

[Execute SQL Task] Error: Executing the query "declare @Now datetime set @Now = GetDate()   Exec ssis.usp_RecordPackageEnd ?,@Now,'Succeeded'  " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Before you ask i read your reply, i've checked the parameter properties etc..

I then downloaded your package from the site, i keep getting the same error message :-(

I think it's this bit that's causing the problem:

declare @Now datetime

set @Now = GetDate()

I copied the usp_RecordPackageEnd stored proc and took out the date stuff, so that the exec looked like this:

Exec ssis.usp_RecordPackageEnd_Test ?,'Succeeded'

This worked! However the original stored proc with date in it doesn't, is it to do with the oledb data provider not liking @Now?

I'd be very grateful for your thoughts.

Many thanks,

Andy R

(York, England)

February 2, 2008 10:12 AM
 

Dale said:

Hi Andrew,

Make sure you have the SP2 installed, there may be a chance that it may not work if you dont have SP2.

Hope this helps...

October 2, 2008 5:09 PM
 

jonathan said:

Why not use the logging feature of SSIS? I'm trying to learn more about the concepts of the instrumentation.

What about be the difference with creating the metrics table etc..... ?

thanks,

Jonahtan

October 3, 2008 11:45 AM
 

andyleonard said:

Hi Jonathan,

  You can use SSIS's built-in logging to do this.

  One reason I use custom logging: I like to own the schema of the log. When some future version of SQL Server is released I would rather avoid surprises. There's a chance the log table schema may change. That's fine but I don't want that change interfering with the SSIS Metrics reporting structure I've built.

:{> Andy

October 5, 2008 6:18 PM
 

MC71 said:

I have a similar problem as andrewyorkbr had.

Do you know what causes the different behaviour:

On my development machine (and many others) the

  declare @Now datetime

  set @Now = GetDate()

works, but on my productive one (which happens to be Windows 2000 Server) it does not, yet any SELECT-Statement with getdate() works. Just when I have some "declare @..." in it, it crashes with the same error "Syntax error, permission violation, or other nonspecific error".

Do you have any idea?

Thank you

Martin

November 25, 2008 12:57 PM
 

MC71 said:

Sorry to bother you with my yesterday's question. I found the solution: Not all of my clients had SP 2 installed, see http://killspid.blogspot.com/2007/01/ssis-bypassprepare.html or http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=376422&SiteID=1

Martin

November 26, 2008 4:53 AM
 

Yasmina said:

Hi,

How I can execute SSIS package using Access 2007 application ?

Thanks in Advance.

Regards,

Yasmina

February 12, 2009 5:25 AM
 

andyleonard said:

Hi Yasmina,

  Goodness, I don't know.

  I opened Access 2007 just now (for the first time, I think) and it appears to use VBA. I suppose you could shell an application from inside VBA and call DTExec.exe.

Hope this helps,

Andy

February 12, 2009 5:47 AM
 

Yasmina said:

Thanks Andy I used exec master.dbo.xp_cmdshell dtexec

It was really helpful

February 12, 2009 7:18 AM
 

andy said:

Was the issue reported by Rhea fixed. I also face the same issue. The problem is it works sometimes and it fails sometimes. When it fails it always gives error:

Exec ssis.usp_RecordPackageEnd ?,@Now,'Succeeded'  " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

March 26, 2009 11:25 AM
 

Dinesh said:

Great work Andy! This works very well with SSIS 2008 as well. Thanks a lot. I reckon this as a very good introductory article for those who are stepping into this domain.

Dinesh

March 26, 2010 7:14 AM
 

Michael Waclawiczek said:

Great post. Found it very informative.

April 30, 2010 8:15 PM
 

RogerZebra said:

Excellent post. Your efforts is much appreciated.

March 9, 2011 9:20 AM
 

RogerZebra said:

Hmm can't get the script to functioning, where I'm supposed to add the script. I tried to add it as a new script and ereased the old script and switched it with this. As you can tell I'm not a wizard in c or vb. So, please if you could take the time to explain that piece would be very much appreciated.

Thanks

March 10, 2011 6:28 AM
 

Kingdom said:

Wow! Great article and Thank you very much.

When I added the scrip task inside the sequence container and configured it, it is showing me a blue line at Dts.Results.Success  running the package the script task failed.

Dim sVersion As String = Dts.Variables("sVersion").Value.ToString

   Dim sMsg As String

   sMsg = "Version: " & sVersion

   MsgBox(sMsg)

   Dts.TaskResult = Dts.Results.Success

Any help pls

Thanks

January 31, 2012 6:20 PM
 

Bob said:

Just wanted to say Thanks a bunch Andy. Although I was looking for something entirely different, reading Part 1 of your series resolved a vexing problem I've been fighting for days! Keep up the excellent work!

August 8, 2012 10:42 AM
 

Sam Enchill said:

Reply to KINGDOM SAID's question:

Depneding on which version of VS you are using, the script task will not work .If you are using MS VB 2008 Script Language, your script within the script task editor should look something like this

Dim sVersion As String = Dts.Variables("sVersion").Value.ToString

       Dim sMsg As String

       sMsg = "Version: " & sVersion

       MsgBox(sMsg)

       Dts.TaskResult = ScriptResults.Success

December 9, 2013 4:37 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement