How to make Application.LoadPackage() and Package.Execute() to run asynchroniously?

Hi,

I am trying to execute a SSIS package programmatically. When a user drops a file in a shared folder, we execute the package based on that file. I am using SqlServer.DTs.Runtime.Application.LoadPackage() and SqlServer.DTs.Runtime.Package.Execute() functions each time to do this.

The problem is, when, say two people drop a file, the second one will not execute untill the first one is completed. I also tried only calling LoadPackage() a single time, and then storing the instance and calling Execute() in a different thread on each file drop; although the blocking still occurs. I assume the Package object is the one doing the blocking then behind the scenes.

Is there any built in functionality to make these functions (Application.LoadPackage() and Package.Execute()) run async? If not, has anyone had much success sticking these calls into a thread? I tried sticking these calls into a thread using System.Threading.ThreadPool.QueueUserWorkItem(), and this appears to work, however it randomly crashes the program when I drop multiple files one after the other. The exception also isn't too helpfull (pasted below):

"The package failed to load due to error 0xC0011008 "Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.". This occurs when CPackage::LoadFromXML fails."

So, is what I am trying to do possible? I know it must be, because when I was using .NET 1.X, I was just calling dtexec via the command line, and dtexec was able to execute many packages simultaneously....

Thanks for any help,

DrewThe calls are synchronous, but each package object is independent - so if you create a thread per incoming file, create a new package object and do LoadPackage/Execute you should get the behavior you need.

The random errors you see are probably caused by your code trying to load the DTSX file before it was fully copied. So SSIS tries to read partial file and throws the error reporting it is not a valid XML file. You need some way to ensure you only load a file when it is ready, e.g. try opening it exclusively until you succeed (it will fail if someone is still writing to the file).

how to make a 'my reports' folder

Hi,
I am having some trouble getting the 'My Reports' folder to appear for all
users. The docs just say that as long as the user exists as a windows user
and the 'My Reports' option is ticked in 'Site Settings', ReportServer
should just create a 'My Reports' folder the first time a user logs onto the
ReportServer. But this is not working for all users. About half of the
users have the 'My Reports' folder created. So I need to know how to create
them manually, or at least some workaround to get each user a 'My Reports'
folder!
I don't see any difference between the user accts (other than the names).
Thanks,
LanceLance,
Have you checked to ensure all users are are included in the "My Reports"
role - or in some custom role that includes the required permissions.
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11849vnplj1n02@.corp.supernews.com...
> Hi,
> I am having some trouble getting the 'My Reports' folder to appear for all
> users. The docs just say that as long as the user exists as a windows
> user
> and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> should just create a 'My Reports' folder the first time a user logs onto
> the
> ReportServer. But this is not working for all users. About half of the
> users have the 'My Reports' folder created. So I need to know how to
> create
> them manually, or at least some workaround to get each user a 'My Reports'
> folder!
> I don't see any difference between the user accts (other than the names).
> Thanks,
> Lance
>|||Hi,
Thanks for the reply, but I am a bit confused about it! Maybe if I explain
a bit more about the setup: The ReportServer is set up with no common
reports. Each user can *only* view the reports in their own 'My Reports'
folder. As such each user *must* have a 'My Reports' folder! (obviously)
Here is how the server is set up:
* There are the standard 'Item-level' roles and an additional one,
'Viewer' - which is a limited acct, that just allows the user to 'view
folders' and 'view reports'. None of the non-admin users can create
reports.
* The 'System Roles' are the standard 'System Administrator' and 'System
User' - the 'System User' has the 'View report server properties' and 'View
shared schedules' tasks.
* The 'Enable My Reports to support user-owned folders for publishing and
running personalized reports.' checkbox is checked in the 'Reporst Server'
'Site Settings'. The 'Choose the role to apply to each user's My Reports
folder' dropdown is 'Viewer'
To set up for a new user:
* Each new user is added to the 'Users' group on the server (windows 2000
server).
* The user logs in, and a 'My Reports' folder should be created.
But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
shown. I don't know how to check whether there is a 'My Reports' folder for
the user - I am thinking that there is a value in the DB somewhere that
tells me...
I was experimenting with a dozen new users, and the docs say that as soon as
a windows user logs in, a 'My Reports' folder should be created. I tried
both creating a 'System Role' for the user and *not* creating a system role,
but the creation of the 'My Reports' folder seems to occur randomly. I say
*seem*, as I am sure that I am missing a step or something, as I confess
that I find the role assignment somewhat confusing. But I'm new, and I am
sure I'll get used to it...
In response to your kindly answer:
So, from my limited understanding, the 'My Reports' role is an 'item-level'
role; I can only seem to assign to this at the report level (ie an 'item')
*not* to a user. But if the 'My Reports' folder is not being created, I
can't assign this 'tiem-level' role to it!
In short: I am still stuck with some users having a 'My Reports' folder and
most users *not* having one!
Thanks,
Lance
> Lance,
> Have you checked to ensure all users are are included in the "My Reports"
> role - or in some custom role that includes the required permissions.
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:11849vnplj1n02@.corp.supernews.com...
> > Hi,
> > I am having some trouble getting the 'My Reports' folder to appear for
all
> > users. The docs just say that as long as the user exists as a windows
> > user
> > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > should just create a 'My Reports' folder the first time a user logs onto
> > the
> > ReportServer. But this is not working for all users. About half of the
> > users have the 'My Reports' folder created. So I need to know how to
> > create
> > them manually, or at least some workaround to get each user a 'My
Reports'
> > folder!
> >
> > I don't see any difference between the user accts (other than the
names).
> >
> > Thanks,
> > Lance
> >
> >
>|||Hi,
I was just nosing around the DB, and I noticed that all the users are in the
'users' table, but the user accounts don't appear in the 'PolicyUserRole'
table, and hence there are not any policies for them.
I don't know if this helps any or just confuses...
And there aren't any application events in the log on the server, either.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11869vtmjhs8i08@.corp.supernews.com...
> Hi,
> Thanks for the reply, but I am a bit confused about it! Maybe if I
explain
> a bit more about the setup: The ReportServer is set up with no common
> reports. Each user can *only* view the reports in their own 'My Reports'
> folder. As such each user *must* have a 'My Reports' folder! (obviously)
> Here is how the server is set up:
> * There are the standard 'Item-level' roles and an additional one,
> 'Viewer' - which is a limited acct, that just allows the user to 'view
> folders' and 'view reports'. None of the non-admin users can create
> reports.
> * The 'System Roles' are the standard 'System Administrator' and 'System
> User' - the 'System User' has the 'View report server properties' and
'View
> shared schedules' tasks.
> * The 'Enable My Reports to support user-owned folders for publishing and
> running personalized reports.' checkbox is checked in the 'Reporst Server'
> 'Site Settings'. The 'Choose the role to apply to each user's My Reports
> folder' dropdown is 'Viewer'
> To set up for a new user:
> * Each new user is added to the 'Users' group on the server (windows 2000
> server).
> * The user logs in, and a 'My Reports' folder should be created.
> But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
> shown. I don't know how to check whether there is a 'My Reports' folder
for
> the user - I am thinking that there is a value in the DB somewhere that
> tells me...
> I was experimenting with a dozen new users, and the docs say that as soon
as
> a windows user logs in, a 'My Reports' folder should be created. I tried
> both creating a 'System Role' for the user and *not* creating a system
role,
> but the creation of the 'My Reports' folder seems to occur randomly. I
say
> *seem*, as I am sure that I am missing a step or something, as I confess
> that I find the role assignment somewhat confusing. But I'm new, and I am
> sure I'll get used to it...
>
> In response to your kindly answer:
> So, from my limited understanding, the 'My Reports' role is an
'item-level'
> role; I can only seem to assign to this at the report level (ie an 'item')
> *not* to a user. But if the 'My Reports' folder is not being created, I
> can't assign this 'tiem-level' role to it!
>
> In short: I am still stuck with some users having a 'My Reports' folder
and
> most users *not* having one!
> Thanks,
> Lance
>
>
> > Lance,
> >
> > Have you checked to ensure all users are are included in the "My
Reports"
> > role - or in some custom role that includes the required permissions.
> >
> > Regards,
> >
> >
> > --
> > Rob Labbé, MCP, MCAD, MCSD, MCT
> > Lead Architect/Trainer
> > Fidelis
> >
> > Blog: http://spaces.msn.com/members/roblabbe
> >
> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> > news:11849vnplj1n02@.corp.supernews.com...
> > > Hi,
> > > I am having some trouble getting the 'My Reports' folder to appear for
> all
> > > users. The docs just say that as long as the user exists as a windows
> > > user
> > > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > > should just create a 'My Reports' folder the first time a user logs
onto
> > > the
> > > ReportServer. But this is not working for all users. About half of
the
> > > users have the 'My Reports' folder created. So I need to know how to
> > > create
> > > them manually, or at least some workaround to get each user a 'My
> Reports'
> > > folder!
> > >
> > > I don't see any difference between the user accts (other than the
> names).
> > >
> > > Thanks,
> > > Lance
> > >
> > >
> >
> >
>sql

how to loop through my workflow by looking at the file system?

Hi, not sure my subject title makes it clear what I want but here it is.

I have a workflow which basically looks at an excel file in a folder on the local drive and then does loads of stuff to it. Everytime I want to process a different excel file that is in the same location al I have to do is change the value of a single local variable, which is just the name of the excel file.

Is there a way to make this automatic? For exmaple....could I somehow put my whole workflow inside a loop that looks inside that local folder and one by one, get the name of the file, assigns the name to that global variable, and then runs the flow...and continues to do that until it gets to the end?

any help would be greatly appreciated...thanks!!!!

andy

Have you look at the ForEachLoop container in the control flow? you can use it to loop through each excel file in a specific folder.

|||awesome, thanks!

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

How to locate the database folder in sql 2005

We want to locate the database folder/files (where the databases are stored) like SQL Server Management Studio UI does,
when you click on attach database / Add.

The question is how to retrieve this folder/files programmatically (C# or VB, SMO?).

For example we want our application client to connect remotely to an SQL Server and attach a new database, using the folder/files obtained from the retrieved method.

If you run Profiler while you use SSMS you will see the code that it runs to populate the dialogs you open.

The code is t-sql so you will have to write your own C to do the same thing

|||

We tried profiler but we didn't find something to help us.

Thank you Anyway.

|||

Perhaps this will point you in the right direction:

SELECT physical_name
FROM sys.master_files
WHERE name = 'Northwind'

A little string manipulation and you should be fine.

|||Thank you but this is for a database already attached.|||

So..., if the database is not attached, then you can put it just about anywhere you wish.

There are 'default' locations, and there are the locations you decide to use. If you are not concerned about currently attached databases, and their locations, then what is the issue?

All servers will have at least master, tempdb, model, and msdb databases. If you can find where they are stored, then you can store your database there too.

|||I don't know the folder and the name of the database. That is why I want to locate it.|||

Remove the WHERE clause and get a list of all databases attached to the server, and their locations.

SELECT
name,
physical_name
FROM sys.master_files

Now... how to determine which one of these listed databases is the database you are seeking since you don't have a database name... You will be very, very, very lucky if there is nothing more than master, tempdb, model, msdb, -AND only the one database you seek.

Did I say, really, really, lucky...

Perhaps adding the following WHERE clause:

WHERE name = db_name()

But that may not always work, the (file) 'name' could be different from the logical database name; an improved query is:

SELECT
db_name( database_id )
name,
physical_name
FROM sys.master_files
WHERE database_id = db_id()

This last query should give you the logical database name, the filename, and the filepath.

|||

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

|||

GeoB wrote:

Verry verry sorry..

I don't know the folder and the name of the database. The database is NOT attached. That is why I want to locate it.

That would have been GOOD information to have earlier.

Since there is no certainly as to how a database file is named (it does not have to have a [mdf] suffix), you may have quite a problem there.

|||

When use SQL Server Management Studio UI you do know where the databases are stored ok?

I want to make something like that: http://www.kenix.eu/georgebakogiannis/LocateDatabaseFiles.png

|||

If you have a default instance on the box, then the default data and log locations are held in the registry at these locations:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\DefaultLog

However there's no guarantee that all database and log files will be found at these locations.

Chris

how to load a set of 10000 xml files to a sql table?

Hi all,
I have a folder with about 10.000 xml files that need to be transferred to a
SQL table.
the structure of the xml file is something like this:
<?xml version="1.0" encoding="UTF-8"?>
<customer>
<id>123</id>
<sex>M</sex>
<firstName>firstname</firstName>
<lastName>Lastname</lastName>
<phone>123123</phone>
<BillingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</BillingAddress>
<ShippingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</ShippingAddress>
</customer>
How can I do this?
Has anyone a sample or a brief description how to do this?
Thank you very much!
Regards, Martin
Martin,
I have just written code for an ACTIVEX controled DTS that does exactly
what you are looking for. Ive copied and pasted the code below. Im just a
jr. developer, but I hope this helps you. You will have to change the code
so that it can "read" your xml files. Also, ive added some links to sites
that helped me accomplish this.
Ben
Links (sorry, i dont know how to do HTML links) (the second link is really
good):
http://www.eggheadcafe.com/articles/20030627b.asp
http://www.perfectxml.com/articles/xml/importxmlsql.asp
my code:
'************************************************* ***********
' Visual Basic ActiveX Script
'************************************************* ***********
Option Explicit
Function Main()
' Declare FSO Related Variables
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
' Import Folder
'global variable "ImportFolder" has a value like: C:\logs\
sFolder = DTSGlobalvariables("ImportFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'reads each file in the folder and moves them to a second
folder
For Each fsoFile in fsoFolder.Files
sFileName = sFolder & fsoFile.Name
call ParseFile(sFilename)
call MoveFile(sFilename)
Next
Main = DTSTaskExecResult_Success
End Function
'************************************************* **********
' ParseFile
' This method takes as input the complete name of an xml file
' (including path) and retrieves specific information from the
' file, as needed for the Webshield SPAM reporting
'************************************************* ***********
Function ParseFile(importFile)
'Declare File Related Variables
Dim objXMLDOM
Dim objNodes
Dim objEventNode
Dim objADORS
Dim objADORS2
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load importFile
'setting the format of the xml files, the eventlog has nodes
called event
Set objNodes = objXMLDOM.selectNodes("/EventLog/Event")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
Set objADORS2 = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=(local);UID=sa;PWD=;DATA BASE=Northwind;"
objADORS.Open "SELECT * FROM Logs", objADOCnn, adOpenKeyset, adLockOptimistic
objADORS2.Open "SELECT * FROM Imported", objADOCnn, adOpenKeyset,
adLockOptimistic
'Record file that was imported
With objADORS2
.AddNew
.fields("FileName") = importFile
.Update
End With
'Import record(s)
For Each objEventNode In objNodes
With objADORS
.AddNew
'read an attribute of the
event
.fields("Date")=
objEventNode.selectSingleNode("@.local-time").nodeTypedValue
'read specific values of the
event
.fields("From Inside Total") =
objEventNode.selectSingleNode("Info[@.name='smtp.fr om_inside.messages']").nodeTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
ParseFile = DTSTaskExecResult_Success
End Function
'************************************************* **********
' MoveFile
' This method moves a file from one folder to another so that the
' file does not get imported into the SQL database twice
'************************************************* **********
Function MoveFile(importFile)
' Declare FSO Related Variables
Dim sFolder
Dim endFolder
Dim fso
Dim fsoFolder
' Import Folder
sFolder = DTSGlobalvariables("ImportFolder")
' Imported Files Folder
endFolder = DTSGlobalvariables("saveFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'move the file
fso.movefile importFile, endFolder
MoveFile = DTSTaskExecResult_Success
End Function
|||Hi Ben,
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
> Martin,
> I have just written code for an ACTIVEX controled DTS that does exactly
> what you are looking for. Ive copied and pasted the code below. Im just
> a
> jr. developer, but I hope this helps you. You will have to change the
> code
> so that it can "read" your xml files. Also, ive added some links to sites
> that helped me accomplish this.
> Ben
Thank you very much! I think this was exactly what I was searching for.
Just one more question:
Do you start the code with a ActiveX Control in a dts package as described
in
http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
Regards, Martin
|||Martin,
I'm not sure I understand your question. The following are the steps to
use to be able to use the code:
1. In enterprise manager, under the specific server you are using, click on
data transfer services and then right click in the right window and create a
new package
2. Right click -> add task -> activeX script task
3. Copy in the code and then modify for your xml files
To set the global variables i use,
close the tasks and make sure non are selected, click package -> properties
and then click the tab global variables.
i hope this answers your question. if not, could you please rephrase it?
thanks.
Ben
"Martin Bübl" wrote:

> Hi Ben,
> "Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
> news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
>
> Thank you very much! I think this was exactly what I was searching for.
> Just one more question:
> Do you start the code with a ActiveX Control in a dts package as described
> in
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
> Regards, Martin
>
>
|||Hi Ben,
thank you, again this was exactly the information I was asking you for.
Regards, Martin
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:C3B288ED-D170-4465-97B1-62EA7AAD0086@.microsoft.com...[vbcol=seagreen]
> Martin,
> I'm not sure I understand your question. The following are the steps
> to
> use to be able to use the code:
> 1. In enterprise manager, under the specific server you are using, click
> on
> data transfer services and then right click in the right window and create
> a
> new package
> 2. Right click -> add task -> activeX script task
> 3. Copy in the code and then modify for your xml files
> To set the global variables i use,
> close the tasks and make sure non are selected, click package ->
> properties
> and then click the tab global variables.
> i hope this answers your question. if not, could you please rephrase it?
> thanks.
> Ben
> "Martin Bbl" wrote: