Calling A Data Pump Task From DTS?
Sep 30, 2005
I have a vbscript to read all files from a directory and, if the file
is valid, I would like my DTS to process it. I tried using the
vbscript as an ActiveX workflow script in the DTS, but it does not
execute the data pump until it has completed looping through all the
files, so only the last file read is sucked into the database
(utilizing a global variable as the filename). Is there a way to
execute the data pump task from within the activex script? I can't
seem to find any documentation about executing a DTS task.
Basically, the workflow I want is:
1)Read files from directory (the number and names may change each
time). (done with vbs)
2)For each file, send it through the transformation into the database.
3)When the information is in the database, append a date to the file
and move it to the archive folder. (done with vbs)
If I am going about this the wrong way and you see something that is
not obvious to me, please let me know.
Thanks in advance!
View 3 Replies
ADVERTISEMENT
Jun 7, 2006
My client is using a sql server 7.0 to store realtime data like heat,temp,pressure etc inserted every second.He wants me to provide a solution to transfer the summarised data to oracle server on a regular basis..say once on every 5 min..
what approach i should take....help me out pls
View 7 Replies
View Related
Aug 28, 2015
I have to value [CreateDate] in the data pump of my Flat File Source into my OLE DB Destination SQL Server Table. With a Variable within the SSIS Package or with a Derived Column task within the Data Flow between the Flat File Source and OLE DB Destination?
View 2 Replies
View Related
Feb 23, 2003
I have a largish DTS package built generically from VB. It uses a combination of DTSExecuteSQLTask and DTSDataPumpTask (using SQLstatements for the source). 18 tasks are failing (1 and 17 respectively by the above types).
When I try to execute the tasks individually I get messages like "Column name xxxx was not found". the column does exist in the table specified in the SQL statements and further the SQL statements execute ok using the Query Analyzer.
If I select Properties, Transformations for a task I get presented with the Verifying Transformations dialog (i.e. indicating there are errors). If I select the third option (Remove all transformations and redo audo-mapping) and save my changes the task then executes okay.
This is driving me insane! Any ideas?
View 3 Replies
View Related
Jul 25, 2007
I did not see a forum for the SQL Server 2000 DTS.
I have a flat file feeding a table via a data pump. The table is only used by this process. It will run for about 30minutes and then fail. The message in the history does not give any detail on why it is failing. Below is the message I get and if I rerun the job it works fine. Anyone help me please.
Date 07/23/2007 6:00:02 AM
Log Job History (Daily: Load EOL from MVS1 (First Run))
Step ID 1
Server PIT-CS-M608
Job Name Daily: Load EOL from MVS1 (First Run)
Step Name Daily: Load tblCaseMasterSched
Duration 00:28:05
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: PIT-CS-M608SYSTEM. ...rt: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSStep_DTSDataP... Process Exit Code 1. The step failed.
View 1 Replies
View Related
Mar 21, 2008
Hi...My first post.
I'm currently creating a SSIS package that takes data from 3 unique databases. A SQL DB, FoxPro DB, and an Oracle DB. The data is pulled, cleansed and put into a single SQL 2005 table. The data is then pulled from this table every 15 minutes, formated in a given specification and uploaded to an ftp site. This part is done. My question is this:
This package needs to run around the clock, non-stop. How can package be set up to do this? It needs to pull data from the 3 DBs and put it in the common table, wait 15 minutes and do it again. Wait 15 more mintues and do it again. And so forth. A problem I'm having is I don't see a way to set up a SSIS package so that it runs around the clock.
On same premise, I have another issue. When I try to take data from the common table and there is nothing there, it causes an error. Is there some way that you can run a test like
SELECT * FROM _table_ WHERE is_sent = 0
if results == 0
{
wait 15 minutes and test again.
}
else if
{
write flat file, wait 15 minutes.
}
This has to be done in the Control Flow scope, so I can't use a conditional split. This is a pretty big deal as this needs to run around the clock. Thank you in advance for your assistance.
View 4 Replies
View Related
May 15, 2006
I am having trouble understanding how the SSIS data pump determines when to decide "The final commit for the data insertion has started/ended". On some tasks the rows are inserted one at a time every few milliseconds (shown by a default getdate() in a datetime column). In others the final commit occurs as I would expect at the end of the data pump task.
There are times i want the data pump task to commit all records that are succesful, row by row and there are times I want an all or none situation. Can somebody explain why this behaviour occurs and how i can control which commit option I want the data pump tasks to use?
View 1 Replies
View Related
Sep 19, 2007
Hi,
I am converting an old, classic asp application to ASP.NET. In the classic app, users are imported daily by importing a file from the head office and placed in the database by an SSIS package. In the new app, we are trying to use the built-in membership/authentication functionality. Does anyone know if it's possible to call the .NET membership API from, say a script task?
There is also the possibility of calling the aspnet_Membership_CreateUser directly, but that looks like I'd have to deal with password hashes and salts.
Any thoughts?
Trey
View 8 Replies
View Related
Jun 6, 2006
I'm attempting to call a storedprocedure from within an ActiveX task ina DTS and am getting a "Command Text was not set for the commandobject" error. I have no problem if I replace the stored procedure callwith the actual SQL.Is it possible to directly call a SP via an ActiveX task? If yes, couldsomebody help me with the syntax please?This is what I have currently haveSet Conn = CreateObject("ADODB.Connection")SQL = "exec (MySP)"set RLoop=conn.Execute(SQL)do while (NOT RLoop.EOF)msgbox RLoop(1)RLoop.MoveNextLoopTIAKarthik
View 1 Replies
View Related
Mar 1, 2008
Hi: I a SSIS beginner and am hoping this is an easy one. I am using a Foreach loop container with an in-memory ADO recordset as the enumerator. The recordset has 3 columns and one of them I wish to fixup data in by calling a web service task that returns an integer. I see how to call the web service and return the integer into a variable but am unsure how to approach putting that variable back into the in-memory ADO recordset? Should I even be trying to do it this way?
thanks!
Henry
View 11 Replies
View Related
Mar 3, 2006
Hello,
I was wondering if someone could point me in the direction of any sample code or documentation that allows you to execute SSIS packages from within a Script task?
In SQL Server 2000 DTS this was accomplished by instantiating the DTS.Package object and calling either the LoadFromSQLServer or LoadFromStorageFile method.
I have not been able to find any similiar logic in the SQL Server 2005 SSIS environment.
Any help would be greatly appreciated.
Thank you,
Gordon Radley
View 6 Replies
View Related
Dec 13, 2005
Hi!
Is is possible to call a SQL task within an ActiveX script task?
View 1 Replies
View Related
Dec 13, 2005
Hi!
Is is possible to call a SQL task within an ActiveX script task?
$3.99/yr .COM!
http://www.greatdomains4less.com
View 1 Replies
View Related
May 23, 2007
Hello,
I'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.
I have tried the following things:
Setting integrated windows authentication in IIS
Setting the NTFS permissions of those web site directories to EVERYONE
Using a credential / proxy in SSIS and running it from SQL Agent
Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account
I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo
Also in the security event log it shows all authentication as successful.
View 2 Replies
View Related
May 6, 2008
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
Thanks
*** Campbell
View 5 Replies
View Related
Nov 6, 2007
Hi ,
Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine
Now the procedure is changed to Oracle.So I have changed it to "BEGIN PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing 'execute BEGIN PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.
Please help me.. thanks in advance
Regards,
GK
View 5 Replies
View Related
Jun 29, 2015
I am searching for a solution for Calling or consume a web service in SSIS through Script task. I have gone through so many links but i am able to find the exact solution. I am getting so many references, though i am unable to crack it.
My requirement is i need to call a web service URL through script task which is having a client certificate. When we are trying to connect to the URL it will ask for the certificate authentication. After calling this URL we will get a WSDL file from the web service, We need to consume that WSDL file and we need to identify the methods inside this WSDL and need to write the data available in this WSDL to the data base tables.
How can we call that web service URL( With certificate) through script task and how can we read the WSDL file and How we can load the data into DB table.
View 8 Replies
View Related
Oct 11, 2006
I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A. The batch file uses pgp software and encrypts a file sitting on machine B itself. The reason why my batch file is sitting on machine B, is because the PGP software is on machine B.
If I execute the batch file by itself from machine B, the script runs fine. I refer the same batch file as a UNC path from my package on machine A. But that does not work since the 'Working directory' is still machine A. I can not set machine B's folder as the working dir because it does not accept UNC path. So I say, ok , let me map a path to that UNC location and map it as drive 'Z:'. Certainly if I do so, I will be running the process on machine A and the batch file will look for the pgp software on machine A, and hence fail.
I have tried third party remote batch execution tools (PSEXEC) but have not had success, not because of SSIS limitations, but simply because the PGP executable when run through the PSEXEC tool, does not identify the location of the public keys on machine B and hence gives an encrytion failure.
How do I get the remote batch file to execute such that it executes with its own env? Is there a better remote execution tool I can try or are there any other features of SSIS I can use to get around this issue? I need the results of the batch file and hence do not want to make it an asyncronous process.
Thanks
Sumeet
View 3 Replies
View Related
Jan 7, 2015
I have a remote batch file on machine B that I need to execute using 'Execute process task' control from a package on machine A.
How Can I achieve this....
View 2 Replies
View Related
Oct 10, 2006
This is the first time I've tried creating an "execute sql task" with a "full result set".
I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.
[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Has anyone else had success with a full result set?
Thanks,
-Lori
View 10 Replies
View Related
Jan 30, 2008
Hello:
I've created a package that runs fine from BIDS when logged in with my domain account. I have created a SQL Agent Proxy on the server with that same account. In the Job Step on the server, I edit the connection strings so that username and password is there for both my source Access connection and the destination SQL Server. Here is the connection string I create for MS Access:
Code SnippetData Source=\10.210.226.202OTM Reports for SymmetricsCDRD001.MDB;User ID=admin;Password=;Provider=Microsoft.Jet.OLEDB.4.0;
Here is the error:
Code SnippetExecuted as user: DOMAINMRUSER. ethod call to the connection manager "MSAccessDB" 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 Error: 2008-01-30 09:49:19.66 Code: 0xC0047017 Source: Cost DTS.Pipeline Description: component "Cost" (1) failed validation and returned error code 0xC020801C. End Error Error: 2008-01-30 09:49:19.66 Code: 0xC004700C Source: Cost DTS.Pipeline
I have tried with various settings in the package for "ProtectionLevel" such as "DontSaveSensitive" and "EncryptSensitiveWithUserKey". I would think that using my account with the proxy the last option would work when running it on the server, since it is essentially the same user running the package, but I'm new to playing with the proxy.
I tried using package configurations but got an error there too, think it couldn't access the file, event though it was on an accessible share--accessible to my account.
Any idea what might be happening here?
Thanks,
Kayda
View 1 Replies
View Related
Nov 7, 2015
I want to call "oracle" stored procedure with output parameter from SSIS ole db command task.
Actually I am able to successfully call the procedure but my Output value is not updating in the mapped column.
I used below PL/SQL query.
DECLARE
IS_VALID VARCHAR2(200);
BEGIN
IS_VALID(
PARAM1 => ?,
PARAM2 => ?,
IS_VALID => IS_VALID
);
? := IS_VALID;
END;
If I try to supply "OUTPUT" word I get error:
"ORA-06550: line 1, column 45:
PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following: . ( ) , * @ % & = - + < / >"
BEGIN
IS_VALID(
?,
?,
? OUTPUT
);
END;
how to receive output parameter value of oledb command while calling oracle stored procedures.
View 4 Replies
View Related
Apr 11, 2007
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
View 6 Replies
View Related
May 4, 2006
Hi,
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
View 17 Replies
View Related
Mar 12, 2008
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination)
Could you tell me why then other is slower?
Thanks.
View 7 Replies
View Related
Oct 2, 2007
I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?
A.
View 14 Replies
View Related
Dec 20, 2007
Hi,
I'm trying to get a record count out of a databse using OLE DB Source and row count tasks but keep getting an error. I set up a variable as int32 and select the variable name in the row count task and when I go to the Input Columns tab to select a field to count, it gives me this error:
Error at Data Flow Task[Row Count[505]]: The component "Row Count" (505) has forbidden the requested use of the input column with lineage ID 32.
I don't even know what this means?
thanks,
View 4 Replies
View Related
Dec 28, 2007
Hi,
I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog:
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx
My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM.
However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots)
http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg
The same package works fine against a similar test table with 150k rows.
http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg
http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg
The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table.
Any hints,advice would be appreciated.
View 18 Replies
View Related
Feb 23, 2007
Hi,
I created a package with SQL 2005. The package gets the Access DB and then inserts it into SQL Server.
If I open the package in .NET, I can see the SQL Task and Data Flow Task. The SQL Task has a property sqlstatementsource, which has the necxessary SQL code to create the tables.
How can I tell the SQL Task to recompile the SQL code if I give it another DB name, because the tables differ and don't map in the Data Flow Task
Thanks
View 3 Replies
View Related
Jul 6, 2005
Hi all,
My Problem is Hoe the Stored Procedure returns data to the Calling Env.If it is fieds values then we can use the OUT Parameters.In Case of My SP returns more number of rows .Then how we capture the data and returs to the Calling Env.i know that we can use the Cursors in Oracle but how is it in SQLSERVER???????pls
pls Help me throw some samll exps.Any help Really appriciate..ThaksRams
View 2 Replies
View Related
Jul 9, 2007
hi,
i am new to SQL. i am trying to retrieve a set of data(i.e. set of rows which satisfies my condition given in the select statement). i want to output this recordset to my calling program i.e C#.net. how could i do this. i was adviced to use temp tables. but i am not sure how to do it. can any one say that using the temp table is the correct way. if so can u give me some sample code.
my proble is
Table name: ProductDetails Table
It has following columns
ProductID Name UnitPrice Category Stock
when i give a category value from front end it should bring me the rows which comes under the given category. there will be many rows under this category. how could i send this set of rows back to my calling program.
regards,
Johny
View 1 Replies
View Related
Aug 1, 2005
Hi, does anyone know how to delete data from the SQL database by
calling the stored procedure in the Visual Basic.NET? Because I did the
Delete hyperlink bounded inside a datagrid. I have already displayed
the appointment date, time in the datagrid so I do not have to input
any values inside it. These are my stored procedures code for deleting:
ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9))
AS
BEGIN
IF NOT EXISTS
(SELECT MemNRIC, AppDate, AppTime
FROM DasAppointment
WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime))
RETURN -400
ELSE IF EXISTS
(SELECT MemNRIC
FROM DasAppointment
WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10))
RETURN -401
ELSE
DELETE FROM DasAppointment
WHERE MemNRIC = @MemNRIC
END
IF @@ERROR <> 0
RETURN @@ERROR
RETURN
DECLARE @status int
EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J'
SELECT 'Status' = @status
Can someone pls help? Thanks!
View 11 Replies
View Related
May 30, 2006
I need to call a stored procedure in order to list products by either a text box based search or by clicking on a category.I am very new to this and would really appreciate any knowledge anyone could share.
View 1 Replies
View Related