Error - Cannot open the datafile

Hi there,

I have written a program that loads a package (SomePackage.dtsx) from the physical drive and executes that. The package does nothing but imports data from a csv file to the Sql server 2005. But I can see that the package is failing continuously. I meant the package.Execute() method is returning a DTSExecResult.Failure. I investigated the Package.Errors property that contains the error collection and found that there are two DTSError objects into the collection.

The first one’s description says that

Cannot open the datafile "D:\SOME.csv".

And the later one’s is

component "SOURCE FLAT FILE COMPONENT" (1) failed the pre-execute phase and returned error code 0xC020200E.

But the most interesting thing is if I execute the package through the Execute package Utility (double clicking onto the SomePackage.dtsx file) ships with Sql server 2005 then it executes fine and works as expected. I have checked the permission of the csv file and it has everyone’s full access.

Can anyone help me on this?

I will appreciate all kind of suggestions.

Thanks

Moim

Hi,

"right click" the "flatfile connection manager"

click on "Edit". there are several properties here that needs to be configured

one of which is the "format".. make sure its "delimited" or something that suits your need.

if it didn't solve your problem try other properties

in this area.

regards,

joey

|||

What is different about when you use your tool vs dtexecui?

Is this the same machine?

Is this the same user running both tools?

|||

Darren,

Basically I can see the only difference is when I am running through the code it is failing and when I am running from the windows explorer it is working.

Nothing more. It is same machine, same user.

Any Idea?

Thanks

Moim

|||

joeydj,

Im afraid, I have created that package programmatically so I dont have a scope to edit that inside the Sql Server BI editor. anyway, I checked out and these properties are set.

Any Idea?

Thanks

Moim

|||Is your file on a mapped drive, by chance?|||

Brammer,

No its on a local drive (drive D: ).

Any other idea?

THanks

Moim

|||

Ok, so same package, same machine, same user, same execution parameters, just a different execution host... does not make much sense.

Try adding logging to the package, something simple like log all warnings and errors to a text file. Any other errors? There must be some difference but that error is not very clear as to what.

Error

Hello,

Following is the code written to call a crystal report having records for branch 3 only:

Dim appl As New CRAXDRT.Application
Dim rpt As New CRAXDRT.Report
Dim dat As CRAXDRT.Database

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set rpt = appl.OpenReport(app.path & "\password.rpt", 1)

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "dsn=invdet"
rs.Open "select * from table1 where branch=3", cn, adOpenStatic, adLockPessimistic

Set dat = rpt.Database
dat.SetDataSource rs, 3, 1
CR.ReportSource = rpt
CR.ViewReport

Error:

The Report shows all the records from "table1". It resembles the same report when I run from Crystal Reports.

i.e. it is not reporting for the above recordset set.My changes are in Bold.

'Get rid of these
'Dim appl As New CRAXDRT.Application
'Dim rpt As New CRAXDRT.Report
'Dim dat As CRAXDRT.Database

Dim Report as CrystalReport1 'This is the dsr file that you create

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set rpt = appl.OpenReport(app.path & "\password.rpt", 1)

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "dsn=invdet"
rs.Open "select * from table1 where branch=3", cn, adOpenStatic, adLockPessimistic

Report.SetDataSource rs, 3, 1
'Make sure CR is the name of the CrystalViewer on your form
CR.ReportSource = Report
CR.ViewReport|||I do not know how to create the dsr file|||What version of Crystal Reports are you using?|||I am using version 8.5|||I'm using version 8.5 Developer Edition. When I installed it, it placed all the necessary dlls on my comuter for me.

To create a dsr file, All I have to do is click 'Project', 'Add Crystal Reports 8.5'. Then follow the directions to create a dsr file.

Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module

Erratic SMO Performance

I have written a console app which uses SMO to automate the scripting and the creating of our database.

For some strange reason the performance varies from blindingly fast to excruciatingly slow and I cannot seem to account for the cause. Can anyone tell me why?

For example, when scripting 100 tables, sometimes it will take 1.5 second to do all of them; sometimes it takes about 1.5 seconds to do each one!

Some debugging uncovered the fact that it is the scripting of DROP statements that is the culprit. Scripting a CREATE statment seems to take between 1 and 70ms, whereas scripting a DROP statement *sometimes* takes the same amount of time, but *sometimes* it takes about 1400ms!

I've tried varying all sorts of things but I can't reliably reproduce the problem. Indeed, when I was debugging it to get these metrics, performance suddenly bounced back and there was no longer a problem to debug. Nevertheless, the slow performance occurs frequently, if not more often than the fast performance. I thought it might be memory problems, but memory usage doesn't seem to be any different between high and low performance scenarios.

Obviously, there could be a better way of using SMO that would side step the problem completely, in which case I'm all ears. :-) Here is the code for scripting all tables to a file.

OnProgress(new ProgressEventArgs(""));

OnProgress(new ProgressEventArgs("Scripting tables..."));

ScriptingOptions scriptOptions = new ScriptingOptions();

scriptOptions.AppendToFile = true; // but file will be deleted first

scriptOptions.FileName = PathHelper.IncludeTrailingBackslash(options.Directory) + Constants.CreateTablesFileName;

scriptOptions.DriAll = false;

scriptOptions.ExtendedProperties = true;

scriptOptions.IncludeHeaders = false;

scriptOptions.Indexes = true;

scriptOptions.ClusteredIndexes = true;

scriptOptions.NonClusteredIndexes = true;

scriptOptions.ScriptDrops = true;

scriptOptions.Triggers = true;

scriptOptions.NoFileGroup = false;

DataTable dt = db.EnumObjects(DatabaseObjectTypes.Table, SortOrder.Name);

DataView view = dt.DefaultView;

view.RowFilter = "(Schema <> 'sys') AND (Schema <> 'INFORMATION_SCHEMA')";

numTables = view.Count;

numSuccessfulTables = 0;

File.Delete(scriptOptions.FileName);

using (StreamWriter writer = new StreamWriter(scriptOptions.FileName, false, new UnicodeEncoding()))

{

writer.WriteLine("USE " + db.Name);

writer.WriteLine("GO");

writer.WriteLine();

}

foreach (DataRowView row in view)

{

Table tbl = db.Tables[row["Name"].ToString(), row["Schema"].ToString()];

// script drop

scriptOptions.ScriptDrops = true;

scriptOptions.IncludeIfNotExists = true;

//ignore diagram stuff

if (tbl.Name == "sysdiagrams")

{

numTables--;

continue;

}

tbl.Script(scriptOptions);

// script create

scriptOptions.ScriptDrops = false;

scriptOptions.IncludeIfNotExists = false;

tbl.Script(scriptOptions);

numSuccessfulTables++;

OnProgress(new ProgressEventArgs(null));

}

Is anyone from MS able to comment on this?|||

I spent a lot of time researching the SMO issue and decided to share what I know and what I have done. Here is some of what I found:

1. It seems like there are very few options to speed up access to SQL Server via SMO objects, at the moment. Several people said they have reported this as a major issue to Microsoft.

2. SMO is used natively by SQL Server 2005, so using DMO or other methods are likely to be deprecated in the future. Use them with caution.

3. SMO Scripting queries SQL Server very excessively. This appears to be the major problem and I haven't found away around it. A likely culprit is that, by default, not all SMO object attributes are retrieved from SQL Server at once. Any properties that are used but not yet retrieved require a round trip to the server.

4. There does not appear to be a class or method which will allow you to script an entire server or database at one time. You need to script each object seperately.

Module sqlas

Private stringCollection As New StringCollection
Private scripter As New Scripter

Sub Main()

Try

Dim selectedServer As New Server("NameOfServer")

' The Server object's DefaultInitFields does not include the IsSystemObject property by default
' This caused a major perfomance hit. These methods cause the Server object to include
' the all properties for the selected DB objects.

selectedServer.SetDefaultInitFields(True)

'***** These method calls were being used to set InitFields seperately rather than all at once
'selectedServer.SetDefaultInitFields(GetType(User), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Table), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(View), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(StoredProcedure), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(UserDefinedFunction), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Trigger), True) ' "IsSystemObject")
'selectedServer.SetDefaultInitFields(GetType(Column), True)
'*******************************

' The Scripter requires a reference to the server selected for scripting.
scripter.Server = selectedServer

' Alters the Scripter.Options because they differ from the default.
scripter.Options.Permissions = True
scripter.Options.DriPrimaryKey = True
scripter.Options.IncludeIfNotExists = True
scripter.Options.NoCollation = True

Dim database As Database = selectedServer.Databases("SelectedDatabase")

'***** These calls decreased time by 50%, but it is still too slow
database.PrefetchObjects(GetType(Table))
database.PrefetchObjects(GetType(User))
database.PrefetchObjects(GetType(View))
database.PrefetchObjects(GetType(StoredProcedure))
database.PrefetchObjects(GetType(UserDefinedFunction))
'*************************

' Location to store the scripts
Using streamWriter As New StreamWriter("PathToWriteTo", False)
For Each user As User In database.Users
If (user.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {user.Urn}, streamWriter)
End If
Next

' ROLES - Scripts Role objects
Console.ForegroundColor = ConsoleColor.Yellow
For Each databaseRole As DatabaseRole In database.Roles
If (databaseRole.Name.StartsWith("db_") = False) Then
ScriptSmoObject(New Urn() {databaseRole.Urn}, streamWriter)
End If
Next

' SCHEMAS - Scripts Schema objects
For Each schema As Schema In database.Schemas
ScriptSmoObject(New Urn() {schema.Urn}, streamWriter)
Next

' TABLES - Scripts Table objects
' The Scripter.Options.DriForeignKeys property is set to false so that FKs are not
' scripted with the table objects. They are scripted at the end of the file.
scripter.Options.DriForeignKeys = False
For Each table As Table In database.Tables
If (table.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {table.Urn}, streamWriter)

' Table specific triggers will be scripted with their respective tables
For Each trigger As Trigger In table.Triggers
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
Next
End If
Next table

' VIEWS - Scripts View objects
For Each view As View In database.Views
If view.IsSystemObject = False Then
ScriptSmoObject(New Urn() {view.Urn}, streamWriter)
End If
Next view

' STORED PROCEDURES - Scripts StoredProcedure objects
For Each storedProcedure As StoredProcedure In database.StoredProcedures
If (storedProcedure.IsSystemObject = False) Then
ScriptSmoObject(New Urn() {storedProcedure.Urn}, streamWriter)
End If
Next storedProcedure

' USE DEFINED FUNCTIONS - Scripts UserDefinedFunction objects
For Each userDefinedFunction As UserDefinedFunction In database.UserDefinedFunctions
If (userDefinedFunction.IsSystemObject) = False Then
ScriptSmoObject(New Urn() {userDefinedFunction.Urn}, streamWriter)
End If
Next

' TRIGGERS - Scripts database level Trigger objects
For Each trigger As Trigger In database.Triggers
If trigger.IsSystemObject = False Then
ScriptSmoObject(New Urn() {trigger.Urn}, streamWriter)
End If
Next

' FOREIGN KEYS - Scripts the ForeignKey objects for all tables in the DB
' The Scripter.Options.DriForeignKeys property is set to true so that FKs will
' be scripted at the end of the file
scripter.Options.DriForeignKeys = True
For Each table As Table In database.Tables
For Each foreignKey As ForeignKey In table.ForeignKeys
ScriptSmoObject(New Urn() {foreignKey.Urn}, streamWriter)
Next

streamWriter.Flush()
End Using

Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

End Sub

''' <summary>
''' Scripts an SMO object
''' </summary>
''' <param name="urn">The Urn of the SqlSmoObject to script</param>
''' <param name="streamWriter">The StreamWriter used to write the script to file.</param>
''' <remarks>None</remarks>
Private Sub ScriptSmoObject(ByRef urn() As Urn, ByRef streamWriter As TextWriter)
stringCollection = scripter.Script(urn)
For Each script As String In stringCollection
streamWriter.WriteLine(script)
Next

End Sub
End Module