Stuck With DTSX Run From Job: Connection Problems
May 21, 2008
Hi all,
My target is to make a job which executes a dtsx package.
I made a simple dtsx which executes a simple "Execute SQL task", added a connection into the connection manager, using sql authentication.
I test the dtsx from bids: runs ok.
Package protectionlevel is set to EncryptSensitiveWithUserKey
Then I build the solution, connect to Integration Services, right click on the MSDB folder, import package.
I select: Package location = File System
Package path is the built dtsx file, and I don't select any Protection level (not sure here). now I see the package under MSDB. Right click > Run Package > Runs OK
I create a job now:
New step of type SQL server integration services package, package source = SSIS package store
If I run the job I get this error.
Executed as user: DOMBEL1allservice. ...9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:43:04 Error: 2008-05-21 16:43:04.86 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-05-21 16:43:05.25 Code: 0xC0202009 Source: Connection manager "sa" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.". End Error Error: 2008-05-21 16:43:05.27 Code: 0xC00291EC Source: insert Execute SQL Task Description: Failed ... The package execution fa... The step failed.
Thanks for any help, best regards
View 1 Replies
ADVERTISEMENT
May 23, 2014
I create a ssis package with have connection DB2 (I install ibm_data_server_client_win32_V97 in server) in sql2008 (run ok). I deployment in SQL 2012. When i run with Visual Studio or run direct package (*.dtsx). File run OK.
But, i run with job have a error as below.
Error: 2014-05-15 14:18:08.82 Code: 0xC0202009 Source: Package Connection manager "TESTING.cardpro1"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80004005 Description: "Unspecified error".
End Error
Error: 2014-05-15 14:18:08.82 Code: 0xC020801C Source: Xuat Report AUDCAR Cardpro-cp_audcar [2] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "TESTING.cardpro1" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error
I run with user full permission. I run job success with dtxs no contain DB2 connection. Do you have a this case?
View 2 Replies
View Related
Jun 13, 2007
I have a dtsx package that is calling another dtsx package, however, if the called upon dtsx package fails with errors or what not, then the calling package does not continue as well. Is there any way to override this such that if the called upon package fails, the downstream actions in that package can stop, but the calling packages downstream actions to continue?
View 3 Replies
View Related
Jun 17, 2008
Hi people.
I just run a new instance of a SQL 2005 64x in my network.
For now all is working right. I have only one problem.
I create a procedure for BI that use DTSX.
Sometime if i try to use from Integration services, these job start and immediatly stop without no error.
Someone can help me ?
thank you in advance.
View 3 Replies
View Related
Jan 15, 2007
Hi all,
i have creatd a dtsx packages, i need to script on that packages.
is it possible to get a script on that corresponding packages..?
Can any one able to help me..?
Thanks
krishnakumar.C
View 3 Replies
View Related
Dec 27, 2007
I have been trying in vain to get a DTSX to return to a vb.net application that it has failed, all i get back is a
ReturnResult = success.
In the dtsx is the following;
Secquence
- Transaction Option set to Required
- FailParentonFaluire set to True
- FailPackageonFaluire set to True
- MaximumErrorCount set to 0
Inside the sequence Container
DataFlowTask
- Transaction Option set to Required
- FailParentonFaluire set to True
- FailPackageonFaluire set to True
- MaximumErrorCount set to 0
ExecuteSQLTask
- Transaction Option set to Required
- FailParentonFaluire set to True
- FailPackageonFaluire set to True
- MaximumErrorCount set to 0
I have run the package from my vb.net application and it runs fine, importing data from a file. I tried running it with a bogus file name that doesnt exsist and it still returned a ReturnResult = success. I need it to return a failed result on any errors, which is what i thought it would do as i have it sey to fail the package on errors throughout each task ... am i missing something ?
View 3 Replies
View Related
Nov 9, 2006
I am having more luck editing the dtsx files directly than using configs. Also for documentation purposes this would be helpful as I don't know enough about xml to find or build a tool to create a style sheet for me.
Thanks!
Ken
View 3 Replies
View Related
Aug 9, 2006
Hi everyone,
After a while I'm come back to SSIS but a drawback appears.
I€™ve copied from our server a folder which contains a few dtsx on my local folder and when I€™m going to open dtproj and then choose any of them:
Error 1 Error loading 'M_Hac_Modelo187.dtsx' : Error al recuperar un generador de clases COM para el componente con CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} debido al siguiente error: 8000401a.. c:ssishacienda_anualM_Hac_Modelo187.dtsx 1 1
..
..
..
(I think that is not necessary to translate into english due to simpleness of the message.)
What the hell is happening? I perfectly remember have been doing the same a month ago and I was able to open them without problems.
TIA
View 2 Replies
View Related
Dec 8, 2006
Hi!
I have a SSIS-package with Excel Connection Manager that fails on our x64-platform with error:
Error: 2006-12-08 06:46:23.77
Code: 0xC0202009
Source: dpd2_philips Connection manager "Excel Connection Manager"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2006-12-08 06:46:23.77
Code: 0xC020801C
Source: Copy Data from Blad1$ to dpd2 dbo philips Task Excel Source [107]
Description: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
End Error
It's works fine on my computer (x86) and if I start the package manually on the x64-server. But I noticed that it runs under 32bit (DTExecUI.exe *32 in taskmanager) when it's started manually.
Any idea? The server use dtexec.exe to start the packages. Maybe it runs under 64bit then?
View 4 Replies
View Related
Jul 23, 2007
I have spent the last couple of weeks going through about 15-20 different examples/tutorials on running DTS packages from inside a C# Windows app (and even asp.net). I must have a mental block, because I can't seem to make it click in my head. I have hundreds of DTS packages saved as .dtsx files. I can double click on them and run them perfectly. What I need to do is be able to run them conditionally from inside a windows app. Could anyone point me to a tutorial or example that could help me with this? I've even looked at some VB source to see if I could work it out, but I keep running up against a wall. I'd appreciate any help anyone can provide.
View 1 Replies
View Related
Jun 24, 2008
Hello,
Please, help me.. I'm trying to execute a packge.dtsx that be save in folder. How can i do, to execute it? I create a job, but always give me errors..
View 9 Replies
View Related
Aug 1, 2007
I have some familiarity with DTS packages, and find them easy ( generally) to debug as you can run them step by step and simply find the offending step. when they fail they also put a message box with the error in the centre of the screen. My system has recently been updated and now uses DTSX packages. I can open and veiw these in visual studio, but i cannot run them one step at a time, and they dont give a nice error message when they fail. how do you debug a DTSX package, or at least make debugging it a little easier?
View 7 Replies
View Related
May 23, 2006
Hi Champs
I want to implement a rutine that watches for specific files in a specified folder.
I've found the WMI sample "File Watcher Task" for SSIS and it looks fine.
My question is now: how shall I run this package; shall I start it at let it run forever?
And also how do I monitor that the package is running?
Many thanks
Rogvi
View 2 Replies
View Related
Aug 15, 2007
Ok, I'm actually adding a SSIS job to my job agent on my test SQL server. Noticed that when I go to my job agent --> add new job, under the steps option, I click new. this then takes me to the new job step window. When I select
Type as SQL Server Integrated Services, I then see some new tabs at the bottom of the form. Under package source I can select File System, SQL Server, or SSIS Package Store, then I have to select the location of the dtsx file.
So my question is, since I can select the actual file (package) I want to run from here, do I really have to import a package to the file system or MSDB under the SQL Integration Services on the server?
It appears to me that its kind of the same thing.
I'm new to this SSIS, SQL DB work, so I'm learning as I go. . . .
View 1 Replies
View Related
Aug 30, 2006
I can create and edit DTSX packages in Visual Studio 2005, but I can't execute them because the start button is grayed out. Can someone help?
I am able to run them using the execution utility, but that's a hassle.
Kathy
View 1 Replies
View Related
May 22, 2008
I created a dtsx with my user account and when I had imported it in SQL server
I get the following error when trying to execute:
Executed as user TESTGISSYSTEM Failed to decrypt protected XML node, Key not vallid for use in specified state.
Even when I remove the password for the dtsx I still get the same error.
I saw the reply's on this
Re: DTSX package will not run from SQL server agent
Thread Starter: jschroeder Started: 13 Aug 2007 10:08 PM Replies: 2
but it was no solution for my problem.
Thx
View 4 Replies
View Related
Dec 14, 2007
Hi,
I want to take the backup of all SSIS packages deployed in the SQL server. I need the backup in DTSX format, how should i do that. Please advice.
View 8 Replies
View Related
Feb 8, 2006
Hi,
When I execute the RSExecutionLog_Update.dtsx, I get an error saying "The task "Set Time period" cannot run on this edition of Integration Services. It requires higher level edition". I am using the Proffesional Edition of SQL2005 and the Integration Services Version is 9.00.1399.00. I have configured the target database RSExecutionLog correctly. Wat could be the problem?
TIA
View 4 Replies
View Related
Oct 19, 2007
I understand that I can use dtutil to provide a password to encrypt the package. However, when I run the encrypted package, I need to provide the same password in order to run it. Is there a way that I can encrypt the package but allow anybody to run it without providing the password?
That is, I want the package to be encrypted so that nobody can load and modify the code in Visual Studio, but I want people to be able to execute it. Is there a way to do this? Similar to generating a exe file?
View 7 Replies
View Related
Feb 15, 2006
Hi all
This is the situation: I've a dtsx package wich creates a tab delimited txt file from a sql server 2005 databasetable. Now this all works just fine. But what I want to do is that the user can choose the destination path of that created txt-file. Right now I've declared the path when I created the flat file source.
On the net I found that a config file for a dts package (sql server 2000 - Dynamic Property Task) could do the trick...
Any help plz?
thx!
View 1 Replies
View Related
Mar 28, 2007
Dear all,
SSIS package created from the wizard couldn't be run from the visual studio editor? The run button is disabled. Is it not possible to run from inside editor as well?
Many thanks,
milan
View 3 Replies
View Related
Aug 16, 2006
Hi everyone,
I'm stuck with this silly thing but I haven't idea how to obtain the path for the log file specified in a SSIS package programatically.
This snippet of code works fine:
Dim pkg As New Microsoft.SqlServer.Dts.Runtime.Package
Dim app As New Microsoft.SqlServer.Dts.Runtime.Application
Dim pkgResults As DTSExecResult
pkgLocation = "C:Documents and Settings40990880Mis documentosVisual Studio 2005ProjectsProyecto de SSIS3Proyecto de SSIS3ProvaPrimera.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()
After execution I'd like to see the log file which is allocated in the same place than DTSX file. But how??
Using pkg.LogProviders.Item(0).Name appears description reference "SSIS log provider for Text files1" but no the name of the file or its path
Thanks in advance for any thought or advice,
View 6 Replies
View Related
Oct 16, 2007
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so
created a new database with text indexing enabled and the following table
create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL)
next
create fulltext catalog remoteSupportCatalog
create unique index ui_remotesupport ON support (ftid)
then
create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog
--------
I then populated some rows and issues a quesry
Select * from support where freetext(problemBody, 'test database')
it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"
SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters>
<asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button
</SelectParameters>
and it doesnt give me back an error or data it does nothing. What am I missing?????
View 7 Replies
View Related
Aug 4, 2006
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is:
SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
The tables are
tblUsers
_______
lUserID
tblDLHistory
__________
lDownloadID
lUserID
lVersionID
What on earth am I doing wrong?
View 3 Replies
View Related
Mar 6, 2008
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company
From dbo.contact1 as a,
(
Select DISTINCT accountno
From dbo.contsupp
Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
) as b
Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
------------------------
|dbo.contact1 |
|------------------------|
|accountno | company |
|------------------------|
|5123 | Alpha |
|4158 | Beta |
|2121 | Gamma |
|5555 | Omega |
------------------------
--------------------------------------------
|dbo.contsupp |
|--------------------------------------------|
|accountno | contact | contsupref |
|--------------------------------------------|
|5123 |Product Inventory|device 01 |
|2121 |product note |the note |
|2121 |Product Inventory|device 02 |
|5123 |Email Address |a@b.com |
|4158 |ladkafndkaldkfna |device stopped|
--------------------------------------------
**note: i am using MSSQL 2000
View 2 Replies
View Related
Feb 27, 2004
Hello,
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
View 3 Replies
View Related
Jun 15, 2008
Hi
I am completely stuck trying to make a query
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader:
Sales Order Ref, Order Date,
1, 01/05/2008
2, 01/05/2008
3, 02/05/2008
4, 02/05/2008
OrderLines:
Sales Order Ref, Part Number
1, 222
1, 234
1, 333
2, 222
2, 555
2, 444
3, 333
3, 111
4, 222
StockCategory
Stock Category, Part Number,
A, 222
B, 234
C, 333
D, 444
E, 111
Stock Group:
Stock Group, Stock Category, Priority
Berr, A, 1
Berr, B, 1
OGD, C, 2
OGD, D, 2
DFID , E, 3
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
I am using the following query:
DECLARE @Period DATETIME
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
SET @Period =’2008-05-01’
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT
COUNT(oh.[Sales Order Reference])
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])
GROUP BY oh.[Sales Order Reference]
Is this possible to do?
Thanking you in advance!!!
View 6 Replies
View Related
Jun 23, 2008
Hi,
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>
Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE
View 6 Replies
View Related
Mar 30, 2007
First off, can you post .dtsx packages to the forum? I always end up cutting and pasting code, which is by is nature incomplete, as it generally has external dependencies (connections, variables).
Perhaps you cannot do so for security reasons?
Second, is there a good blog site that allows you to post .dtsx packages?
I'd rather post .dtsx links than cut and paste code (which half the time formats the code un-recognizably), or even worse, post package xml, which contains the layout information and possibly IL that makes it all but unreadable.
View 4 Replies
View Related
Jan 17, 2008
am trying to execute a dtsx package using xp_cmdshell
when testing, this works fine
DECLARE @returncode int
EXEC @returncode = master..xp_cmdshell 'dtexec /f "C:WorkWarehouseDev.ETLLoadGroup_Daily.dtsx"'
PRINT @returncode
then change it to look at the live one
DECLARE @returncode int
EXEC @returncode = master..xp_cmdshell 'dtexec /f "C:WorkWarehouse.ETLLoadGroup_Daily.dtsx"'
PRINT @returncode
doesnt work - it tries to execute the WarehouseDev version
any ideas ??
n.b. didnt know if this should be in this forum or a t-sql forum - apologies if its in wrong place !!
View 4 Replies
View Related
Jul 20, 2007
Hi,
I want to make an application that fills in the variables into a dtsx package (ssis).
I'm able to read the variables created in the package
Application app = new Application();
Package p = app.LoadPackage(pkg, null);
Connections myConns = p.Connections;
foreach( Variable v in p.Variables){
Console.WriteLine(v.Name);
Console.WriteLine(v.Value);
Console.WriteLine(v.Namespace);
Console.WriteLine("/////////////////////");
Console.WriteLine("/////////////////////");
}
i'm also able to add on
Variable myVar = p.Variables.Add("amyCustomVar", false, "User", "3")
When the applications is closed the package does not contain the variable. So this is all done in memory.
Is their a way to actually write the variable into the physical package.
View 1 Replies
View Related
Apr 4, 2007
hi there,
when i start SQL Server business intelligence developer and create new Integration Service project, i will see following error:
Error loading 'Package.dtsx' : Object reference not set to an instance of an object.. C:SairiMy DocumentsVisual Studio 2005ProjectsIntegration Services Project12Integration Services Project12Package.dtsx
this error occures just on my PC and i reinstalled VS2005 and SQL2005 again and unfotunately the problem existes.
please someone helps me (just don't tell me to format my PC!!!)
tnx
View 10 Replies
View Related
Mar 27, 2007
How dow you schedule a file(.dtsx)(I think it is a package) that was created in the SQL Server BI Design Studio to run? I can locate the file I created in SQL Server Management Studio and see it in the right hand pane and even edit it. I can run it in the Design Studio, but cant see how to do that in Management Studio nor how to schedule it.
This is my first attempt at recreating a DTS package using SQL Server tools instead of the Legacy conversion wizard, and I think my brain has overheated. I just cant find it on the Management Screen or the Design. I am also not searching correctly anymore on books online because I cannot find it.
Thanks for pointing me to where I need to go or do.
Tracey
View 5 Replies
View Related
