Classroom SSIS training

Can anyone recommend a live classroom environment SSIS training course? Anwhere in the U.S. would be fine.

Thanks,

Ken

Idea Integration provides hands on training for SSIS, SSAS, and SSRS. I think we have some classes coming up too. Send me an e-mail at jason.gerard AT idea.com and I'll get you the info.|||

You might try Symphic Technology. (Url: www.symphic.com.) They provide training out of Manhattan, NY but also do on-sites.

I saw their booth at Boston TechEd a couple months ago and they looked pretty good.

|||Please e-mail any further training requests to bknight AT whiteknighttechnology.com OR bill.bickford AT idea.com. Thanks.

Classroom SSIS training

Can anyone recommend a live classroom environment SSIS training course? Anwhere in the U.S. would be fine.

Thanks,

Ken

Idea Integration provides hands on training for SSIS, SSAS, and SSRS. I think we have some classes coming up too. Send me an e-mail at jason.gerard AT idea.com and I'll get you the info.|||

You might try Symphic Technology. (Url: www.symphic.com.) They provide training out of Manhattan, NY but also do on-sites.

I saw their booth at Boston TechEd a couple months ago and they looked pretty good.

|||Please e-mail any further training requests to bknight AT whiteknighttechnology.com OR bill.bickford AT idea.com. Thanks.

Class and Sequence Diagrams describing SSIS framework (for Custom Components)?

Hi,

Can anyone tell me where I might find the Class and Sequence Diagrams for the SSIS framework (for Custom Components)?

I've just started trying to create some Custom Transform Components and

I'm really struggling to get my head around the component lifecycle

(i.e what methods are called when, with what arguments, and why) with

just the BOL documentation to guide me.

Thanks in advance,

Lawrie

Take a look here... http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en

The SSIS book by wrox has a few chapters dedicated to creating Source, Destinations, and Transformation components.

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

Child packages : Failed to acquire connection

Hi!

I am having a problem with a Parent package that invokes Child SSIS packages.

The Child packages have EncryptSensitiveWithPassword as their security setting.

I have placed the PackagePassword for each Child in the Parent package.

Each Child package contains a SQL Server Authentication username and password; they are not using Windows Authentication for the SQL Server login.

Here are what seem to be the relevant entries from a log file when the failure occurs:

Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDataInitialize::GetDataSource'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDataInitialize::GetDataSource succeeded'. The external request has completed.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_pre: The object is ready to make the following external request: 'IDBInitialize::Initialize'.
Useriagnostic,XXXX,YYYYY\xxxx,Microsoft OLE DB Provider for SQL Server,GUID1,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,0,0x,ExternalRequest_post: 'IDBInitialize::Initialize failed'. The external request has completed.
OnError,XXXX,YYYYY\xxxx,SQLTask1 in ChildPackage1,GUID3,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

OnError,XXXX,YYYYY\xxxx,ChildPackage1,GUID4,GUID2,06/01/2007 11:14:04 AM,06/01/2007 11:14:04 AM,-1073573396,0x,Failed to acquire connection "Microsoft OLE DB Provider for SQL Server". Connection may not be configured correctly or you may not have the right permissions on this connection.

When I run the Child package by itself, it works without any errors. So the problem seems to be related to the fact that it is being called by the parent, and somehow the "security settings" for the parent are not quite the same as the settings when I run the Child package by itself.

What might I be doing wrong?

I am using File System storage for my packages, on a shared network drive.

Just to check - you are setting the password in the Execute Package task, right? My apologies if this is obvious, just wanted to confirm. |||

Yes, I have set the password in each Execute Package task -- and the error message is not about an inability to open the package (for which there appear to be a number of error messages available). Instead, it is one that seems to indicate an inability to login to SQL Server with the SQL Server Authentication incorporated in the package.

I have read some web pages (e.g., the link below) that refer to the Integrated Security of a SQL Server connection. I see what appears to be some XML for that in the Child packages (but none in the Parent package) -- and the value that I found was "True". I tried changing it to "SSPI", but then I couldn't even run the child package by itself, nor when called by the Parent.

http://blogs.msdn.com/suryaj/archive/2006/05/09/594039.aspx

|||

Is it possible to use the Debugger in Visual Studio 2005 to study the connection parameter values that are present when a Parent package invokes a Child package?

Dan

|||

I'm going to take a look at using the script Jamie Thompon wrote, as found at

http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

I hope it will give me some clues as to why the connection cannot be acquired when the SSIS package is run as a child package.

|||

John,

Using Jamie Thomson's "script task" I could see that you were correct: my problem was still with the package password. I must have typed it incorrectly, or something.

I am sorry to have dismissed your suggestion so quickly -- but I didn't see it until I was off on vacation and happened to logon and notice your post. So I didn't have SQL Server at my disposal to investigate further. You have my sincere apologies.

Dan

|||No problem - I'm glad you were able to resolve it.

Checkpoint file used by another process

I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.

One out of two runs will fail with one of the below errors:

The checkpoint file \\xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.

Checkpoint file \\xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces

I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.

This is causing the entire job to fail even though the control flow was successful.

Anyone encounter this issue? Any assistance is appreciated.

Thanks!

I am under the impression that the package (process) controls checkpoints not the tasks. Checkpointing is at the package level, you only enable tasks to use it.

This is interesting...maybe someone can shed light on it.

What happens if you try to run the two DFTs in sequence?

|||

Philips-HCR wrote:

I have an SSIS solution with 8 packages in it. I have checkpoint turned on with the 'If Exists' option. Each of the 8 packages have 8 separate checkpoint files specified.

One out of two runs will fail with one of the below errors:

The checkpoint file \\xxxxxxxx is locked by another process. This may occur if another instance of this package is currently executing.

Checkpoint file \\xxxxxxxx failed to open due to error 0x80070020 "The process cannot access the file because it is being used by another proces

I have checked all the settings and everything looks fine, looks like the problem is when you have many Control Flow tasks in a package and if two of them are completed at the same time and they try to write to this file one of them is unable to write and it fails.

This is causing the entire job to fail even though the control flow was successful.

Anyone encounter this issue? Any assistance is appreciated.

Thanks!

Based on my own experiences I doubt that is the problem. I have had multiple concurrent tasks writing to a checkpoint file withno problem at all. And Ravi is right, they are all written by the same thread (i.e. the one running the package) not by a seperate thread for each task. I suspect your idea about concurrent tasks is a red herring.

Get hold of Process Monitor (http://www.microsoft.com/technet/sysinternals/default.mspx) to find out what is holding onto those files.

-Jamie

|||I have not tried it just because there are numerous tasks and the check point file that fails is not always the same either. Let me try one at a time to isolate the problem. Thanks for the advice!|||Good idea, let me try that. Thanks!|||

Philips-HCR wrote:

I have not tried it just because there are numerous tasks and the check point file that fails is not always the same either. Let me try one at a time to isolate the problem. Thanks for the advice!

By the way, are you running the 8 packages in series or parallel?
You might want to try running them in serial too.

checking for control files in SSIS

have a job that loads data from a data file into a table.

Is there a "task" that can be used to check to see if a file exists

and put error handling around it without programming ?

have you considered using the file watcher task? http://www.sqlis.com/default.aspx?23

Checking conditional in SSIS

I am converting a DTS package to SSIS.

DTS has the following steps:

1. SQL task that returns a variable ( a count ).

2. ActiveX script:

Function Main()

If DTSGlobalVariables("gsPrevProcessCount").Value > 0 Then
MsgBox "The data for one or more of your members has already been processed. Please review your data files and remove the processed files from the data directory."
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_Success
End If

End Function

In SSIS, I have an Execute SQL Task that returns a variable PrevProcessedCount. Now, I am stuck. How do I display a message to the user (or maybe just put it in the audit log) and how to I check the variable and stop processing my package?

Linda

See if this helps any:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1699966&SiteID=1|||Spiffy! Thanks!

Check the SSIS FTP Connection String

Hi,

i have a table like

No

FileLocation

UserName

Password

1

ftp://sarvi3/test/

xyz

xyz

2

ftp://pandit3/test/test1

abc

abc

3

ftp://katta1/test/test2

klm

klm

i want to check the filelocation is valid (exist) or not before transfering the file from the location. can anyone help me out to resolve

thanks

Sun

Would any checking not be part of the actual transfer code? perhaps some sort of error trapping?

I've always found FTP to be a pain... although I'm willing to accept that could well be down to me rather than any shortcomings in FTP :)

Is this really a TSQL related question? I'd have thought you'd get more joy from one of the scripting forums.