Showing posts with label ran. Show all posts
Showing posts with label ran. Show all posts

Wednesday, March 21, 2012

DTS Package Scheduled Job Result Issue

I am using SQL Server 2000. I created Package1, Package2, and Package3. If I ran from scheduled job Package2 and it failed, the result in Job History is Failed. If I ran from scheduled job Package1 that runs Package2 and then Package3 and Package2 failed, the result in Job History on Package1 is Successful. Why? How can I have the result Failed so I can get the failure notification.

Thanks,

DanYeung

look at DTS Package Properties at Logging tab; there is a check box "Fail package on first error", see if it is on or off.|||

I couldn't find the Logging tab on the DTS Package itself (atually there is no properties when I right clicked) and the schedule job under SQL Server Agent - Jobs. The tabs on the schedule job are General, Steps, Schedules, and Notifications. I am using SQL 2000. Can you please advise? Thanks

DanYeung

|||

in "locale packages" of DTS in Enterprise open yor package, right click on window surface and then "Package Properties"

|||

It works. I have to check it in all 3 pacakges. Thank you.

DanYeung

DTS Package Scheduled Job Result Issue

I am using SQL Server 2000. I created Package1, Package2, and Package3. If I ran from scheduled job Package2 and it failed, the result in Job History is Failed. If I ran from scheduled job Package1 that runs Package2 and then Package3 and Package2 failed, the result in Job History on Package1 is Successful. Why? How can I have the result Failed so I can get the failure notification.

Thanks,

DanYeung

look at DTS Package Properties at Logging tab; there is a check box "Fail package on first error", see if it is on or off.|||

I couldn't find the Logging tab on the DTS Package itself (atually there is no properties when I right clicked) and the schedule job under SQL Server Agent - Jobs. The tabs on the schedule job are General, Steps, Schedules, and Notifications. I am using SQL 2000. Can you please advise? Thanks

DanYeung

|||

in "locale packages" of DTS in Enterprise open yor package, right click on window surface and then "Package Properties"

|||

It works. I have to check it in all 3 pacakges. Thank you.

DanYeung

Sunday, March 11, 2012

DTS package does not exist?

I ran the DTS package using the DTSRun as following:
SET @.action = 'DTSRun /S'+@.server+' /U "sa" /P "correctpassword" /N
"DataMover" '
exec master.dbo.xp_cmdshell @.action
It ran fine in one server. And failed in another server. The error message
is:
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
Error string: The specified DTS Package ('Name = '"DataMover"
'; ID.VersionID = {[not specified]}.{[not specified]}') d
oes not exist.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
The verified the DTS package named as "DataMover" does exist on the problem
server. The DTS packages in two servers are exactly the same. I'm confused
why it worked on one and failed on the other one.
Thanks a lot,
FLXI changed the
/N "DataMover"
to
/NDataMover
It woked about the change. I still don't understand why it performs
differently on two servers. FLX
"FLX" <nospam@.hotmail.com> wrote in message
news:%23paHNQDDEHA.4080@.TK2MSFTNGP09.phx.gbl...
> I ran the DTS package using the DTSRun as following:
> SET @.action = 'DTSRun /S'+@.server+' /U "sa" /P "correctpassword" /N
> "DataMover" '
> exec master.dbo.xp_cmdshell @.action
>
> It ran fine in one server. And failed in another server. The error message
> is:
> Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
> Error string: The specified DTS Package ('Name = '"DataMover"
> '; ID.VersionID = {[not specified]}.{[not specified]}')
does not exist.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
>
> The verified the DTS package named as "DataMover" does exist on the
problem
> server. The DTS packages in two servers are exactly the same. I'm confused
> why it worked on one and failed on the other one.
> Thanks a lot,
> FLX
>
>|||Hello,
The behavior you described is mostly like a bug mentioned below:
308801 FIX: DTSRUN Fails with Errors If the Arguments Have Multiple Space
http://support.microsoft.com/?id=308801
To resolve this problem, obtain the latest service pack for Microsoft SQL
Server 2000. For additional information, click the following article number
to view the article in the Microsoft Knowledge Base:
INF: How to Obtain the Latest SQL Server 2000 Service Pack
http://support.microsoft.com/?id=290211
Please check the service pack on your 2 servers.
321185 HOW TO: Identify Your SQL Server Service Pack Version and Edition
http://support.microsoft.com/?id=321185
If the services packs on your 2 servers are different, we can explain why
it performs differently on two servers.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Sunday, February 19, 2012

DTS importing only new records

Hi!
How can I set up a DTS that reads a flat file database and inserts only the new records to an existing table? This DTS will be ran everyday against a flat file that accumulates records.Import data into temp table and compare with ur original table, delete duplicate records from temp table and insert records from temp table to ur original table|||Alternatively, look at the Data Driven Query Task.

1. On the Source tab, write your Query to Pull data from your source (in this case a flat file).
2. On the bindings tab, specify the destination table.
3. On the Transformation tab, a default transform will be created for you in VB Script. Keep it, but it is most likely that you will have to make some changes.
4. On the Queries tab, you will need to specify a query for inserting data and a query for updating data. If there are many columns, this can be a real pain.
5. On your lookups tab, you will need to specify a Query that will return an integer value (Such as SELECT Count(*) FROM MyTable WHERE PK = ?). Leave the ? exactly as it is written above; that is a parameter that you will pass in from the transform statement.
6. Now go back to the Transform tab; add an If...Then...Else statement around the line where it says Main = DTSTransformstat_UpdateQuery

If DTSLookups("MyLookupName").Execute(DTSSource("MyParameter")) > 0 Then
Main = DTSTransformstat_UpdateQuery
Else
Main = DTSTransformstat_InsertQuery
End If

This block basically says If there exists a record in the destination table with the same Primary Key, then run the Update Query, otherwise run the Insert Query.

7. Now go back to the Queries tab and add your INSERT and UPDATE queries. They should look something like:

INSERT:
INSERT INTO MyTable VALUES (?,?,?,?,?)

UPDATE:
UPDATE MyTable SET Value1 = ?, Value2 = ?, Value3 = ?, Value4 = ?
WHERE PK = ?

Finally, on the queries tab, make sure you align the incoming parameters with the place holders (question marks) using the mapping tool at the bottom of the window. It is, no doubt about it, a pain in the a$$.

Another alternative might be to write a single stored procedure that would accept the incoming values, test for the existence of the primary key and perform an INSERT or UPDATE as required.

Regards,

hmscott|||Hi!

Thanks for the detailed suggestion. I'm new to this stuff and I really want to know more about utilizing DTS to maintain and manipulate my tables.

Anyhow, I was able to setup 2 connections (Flat file connection that points to the flat ascii file and an SQL server connection to my destination table) and a Data Driven Query Task. There's no workflow between them, is this correct? When I execute he package, it failed and gave me an error saying: "statement could not be prepared, Line 2: incorrect syntax near ".".

Here's my transformation code:

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("MID") = DTSSource("Col001")
DTSDestination("IIN") = DTSSource("Col003")
DTSDestination("ITN2") = DTSSource("Col004")
DTSDestination("ITN") = DTSSource("Col005")
DTSDestination("ITN3") = DTSSource("Col006")
DTSDestination("GENDER") = DTSSource("Col007")
DTSDestination("LANG") = DTSSource("Col008")
DTSDestination("TITLE") = DTSSource("Col009")
DTSDestination("NAME") = DTSSource("Col010")
DTSDestination("ADDRESS") = DTSSource("Col011")
DTSDestination("APT") = DTSSource("Col012")
DTSDestination("CITY") = DTSSource("Col013")
DTSDestination("STREAM") = DTSSource("Col014")
DTSDestination("PROV") = DTSSource("Col015")
DTSDestination("POSTCODE") = DTSSource("Col016")
If DTSLookups("Count_ITN2").Execute (DTSSource("Col004")) > 0 Then
Else
Main = DTSTransformstat_InsertQuery
End If
End Function