Duplicate Record Problem

I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
My first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.In SQL Server duplicates are eliminated by using Unique constraint or Index on the index you can add IGNORE_DUP_KEY option and make it all inserts because that option will not affect update statements. And they are very slow but the UNION operator also eliminates duplicates by applying implict Distinct. Run a search for all of the above in SQl Server BOL(books online). Hope this helps.|||For an import approach, I'd suggest inserting the records into atemporary table first. Then select out the unique records and insertthem into your destination table.
For the above query, would something like this work?
SELECT DISTINCT
PatientID,
MRNumber,
PatientFirstName,
PatientLastName,
PatientDOB,
PatientSex,
NewPatient,
...etc, etc, etc...
FROM
(
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr,tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr,tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
) AS SQ|||

I have re-written this a bit to remove the CROSS JOIN syntax which I think is less efficient than the INNER JOIN that is implied in the WHERE clause:
SELECT A.PatientID, D.MRNumber, A.PatientFirstName, A.PatientLastName, A.PatientDOB, A.PatientSex,
A.NewPatient, A.HomePhone, A.WorkPhone, A.Insurance1, D.Ins1CertNmbr, A.Insurance2,
D.Ins2CertNmbr, A.Insurance3, D.Ins3CertNmbr, A.ApptDate, A.ApptTime
FROM tblAppointments A INNER JOIN tblPTDEMO2 D on A.PatientID = D.MRNumber
WHERE A.Insurance1 = 'MED'
AND A.ApptTypeID <> 'MTG'
AND A.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By A.ApptDate

Now, I assume that the duplicates are in thetblPTDEMO2table. Tell me more about these: are the IDs duped?

|||Thanks for your response. The query you wrote is conceptually what I am trying to get, but it throws a syntax error. I've played with a number of variations of this and I can't seem to get it to work.
The other option of using a temporary table during the DTS package is viable, but once again, I can't seem to get the query right. I think what I want is something like:
Insert Into tblAppointments AppointmentKey, PatientFirstName, PatientLastName, ...VALUES (Select DISTINCT AppointmentKey, PatientFirstName, PatientLastName, ...)
but once again, I can't seem to get the syntax correct. Any help would be greatly appreciated. Thanks.|||Thanks for your response. Actually, the tblAppointments table is the one with the duplicates, and yes, the IDs are duplicated as well.

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.
Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==
>X-WBNR-Posting-Host: 84.56.32.28
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>
|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.Applicatio nClass.GetDtsServerPackageInfos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDts ServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:

> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> new
>
|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==
>X-WBNR-Posting-Host: 84.147.181.254
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.Applicati onClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDt sServerPackageInfos(String
[vbcol=seagreen]
>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
to[vbcol=seagreen]
you[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
tree[vbcol=seagreen]
starting
>
|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000?
"Peter Yang [MSFT]" wrote:

> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> Explorer
> eInfos(String bstrPackageFolder, String bstrServerName)
> to
> you
> rights.
> the
> tree
> starting
>
|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==
>X-WBNR-Posting-Host: 84.147.129.198
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default[vbcol=seagreen]
>instance of SQL 2000?
>
>"Peter Yang [MSFT]" wrote:
rights.[vbcol=seagreen]
>Microsoft.SqlServer.Dts.Runtime.Wrapper.Applicati onClass.GetDtsServerPackag
>Microsoft.SqlServer.Dts.Runtime.Application.GetDt sServerPackageInfos(String
click[vbcol=seagreen]
so[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl[vbcol=seagreen]
save[vbcol=seagreen]
Package"[vbcol=seagreen]
create a
>
|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:

> Hello,
> You could still store SSIS package in filesystem other than msdb under this
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues when
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> that
> default
> rights.
> click
> so
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
> save
> Package"
> create a
>

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>X-WBNR-Posting-Host: 84.56.32.28
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackageInfos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:
> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >X-WBNR-Posting-Host: 84.56.32.28
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >Subject: Dumb question about SSIS
> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >Lines: 7
> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >I just imported data from one database to another, I specified to save the
> >SSIS package on the database server (not on the file system).
> >Now I cannot find the package anywhere. There isn't a "Local Package" tree
> >like in 2000 Enterprise Manager. I can't even find a shortcut for starting
> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
> new
> >project in VS, not how to open an existing package.
> >
> >
>|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==>X-WBNR-Posting-Host: 84.147.181.254
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
>> Hello,
>> You could click "Connect" button in Server management studio and click
to
>> select "Integration services", and then type the instance information
you
>> want.
>> Hope this is helpful.
>>
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>>
>> --
>> >Thread-Topic: Dumb question about SSIS
>> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>> >X-WBNR-Posting-Host: 84.56.32.28
>> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >Subject: Dumb question about SSIS
>> >Date: Fri, 25 Nov 2005 10:56:02 -0800
>> >Lines: 7
>> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >I just imported data from one database to another, I specified to save
the
>> >SSIS package on the database server (not on the file system).
>> >Now I cannot find the package anywhere. There isn't a "Local Package"
tree
>> >like in 2000 Enterprise Manager. I can't even find a shortcut for
starting
>> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
>> new
>> >project in VS, not how to open an existing package.
>> >
>> >
>>
>|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000'
"Peter Yang [MSFT]" wrote:
> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==> >X-WBNR-Posting-Host: 84.147.181.254
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >Subject: RE: Dumb question about SSIS
> >Date: Mon, 28 Nov 2005 04:31:02 -0800
> >Lines: 76
> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Thanks Peter, exactly what I was looking for.
> >But now I still cannot see my package, as I'm getting following error
> >message when clicking on MSDB under "Stored Packages" in the Object
> Explorer
> >tree. Any idea why?
> >
> >Client unable to establish connection
> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> >
> > at
> >Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
> eInfos(String bstrPackageFolder, String bstrServerName)
> > at
> >Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
> >sPackageFolder, String sServerName)
> >
> >"Peter Yang [MSFT]" wrote:
> >
> >> Hello,
> >>
> >> You could click "Connect" button in Server management studio and click
> to
> >> select "Integration services", and then type the instance information
> you
> >> want.
> >>
> >> Hope this is helpful.
> >>
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >>
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >>
> >> --
> >> >Thread-Topic: Dumb question about SSIS
> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >> >X-WBNR-Posting-Host: 84.56.32.28
> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >Subject: Dumb question about SSIS
> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >> >Lines: 7
> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> >MIME-Version: 1.0
> >> >Content-Type: text/plain;
> >> > charset="Utf-8"
> >> >Content-Transfer-Encoding: 7bit
> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >Content-Class: urn:content-classes:message
> >> >Importance: normal
> >> >Priority: normal
> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >I just imported data from one database to another, I specified to save
> the
> >> >SSIS package on the database server (not on the file system).
> >> >Now I cannot find the package anywhere. There isn't a "Local Package"
> tree
> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
> starting
> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
> >> new
> >> >project in VS, not how to open an existing package.
> >> >
> >> >
> >>
> >>
> >
>|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==>X-WBNR-Posting-Host: 84.147.129.198
>From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default
>instance of SQL 2000'
>
>"Peter Yang [MSFT]" wrote:
>> Hello,
>> To understand the issue better, will you confirm the default instance is
>> SQL server 2005 instance? SSIS does not support named instance anyway.
>> Also, from the error message, it seems that client force encryption but
>> server does not have certificate installed. Please check the following
>> settings:
>> Run SQL Server 2005 Network Configuration, right click SQL native client
>> network conigurations->Properties, and configure "force protocol
>> encryption" to NO.
>> Right click the protocol under server network configuration and also
>> configure "force protocol encryption" to NO.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
rights.
>>
>> --
>> >Thread-Topic: Dumb question about SSIS
>> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==>> >X-WBNR-Posting-Host: 84.147.181.254
>> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>> >Subject: RE: Dumb question about SSIS
>> >Date: Mon, 28 Nov 2005 04:31:02 -0800
>> >Lines: 76
>> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>> >MIME-Version: 1.0
>> >Content-Type: text/plain;
>> > charset="Utf-8"
>> >Content-Transfer-Encoding: 7bit
>> >X-Newsreader: Microsoft CDO for Windows 2000
>> >Content-Class: urn:content-classes:message
>> >Importance: normal
>> >Priority: normal
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >Newsgroups: microsoft.public.sqlserver.server
>> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >Thanks Peter, exactly what I was looking for.
>> >But now I still cannot see my package, as I'm getting following error
>> >message when clicking on MSDB under "Stored Packages" in the Object
>> Explorer
>> >tree. Any idea why?
>> >
>> >Client unable to establish connection
>> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
>> >
>> > at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
>> eInfos(String bstrPackageFolder, String bstrServerName)
>> > at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
>> >sPackageFolder, String sServerName)
>> >
>> >"Peter Yang [MSFT]" wrote:
>> >
>> >> Hello,
>> >>
>> >> You could click "Connect" button in Server management studio and
click
>> to
>> >> select "Integration services", and then type the instance information
>> you
>> >> want.
>> >>
>> >> Hope this is helpful.
>> >>
>> >>
>> >> Best Regards,
>> >>
>> >> Peter Yang
>> >> MCSE2000/2003, MCSA, MCDBA
>> >> Microsoft Online Partner Support
>> >>
>> >> When responding to posts, please "Reply to Group" via your newsreader
so
>> >> that others may learn and benefit from your issue.
>> >>
>> >> =====================================================>> >>
>> >>
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> >>
>> >>
>> >> --
>> >> >Thread-Topic: Dumb question about SSIS
>> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==>> >> >X-WBNR-Posting-Host: 84.56.32.28
>> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
>> >> >Subject: Dumb question about SSIS
>> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
>> >> >Lines: 7
>> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>> >> >MIME-Version: 1.0
>> >> >Content-Type: text/plain;
>> >> > charset="Utf-8"
>> >> >Content-Transfer-Encoding: 7bit
>> >> >X-Newsreader: Microsoft CDO for Windows 2000
>> >> >Content-Class: urn:content-classes:message
>> >> >Importance: normal
>> >> >Priority: normal
>> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> >> >Newsgroups: microsoft.public.sqlserver.server
>> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> >> >Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >> >
>> >> >I just imported data from one database to another, I specified to
save
>> the
>> >> >SSIS package on the database server (not on the file system).
>> >> >Now I cannot find the package anywhere. There isn't a "Local
Package"
>> tree
>> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
>> starting
>> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to
create a
>> >> new
>> >> >project in VS, not how to open an existing package.
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:
> Hello,
> You could still store SSIS package in filesystem other than msdb under this
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues when
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> >Thread-Topic: Dumb question about SSIS
> >thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==> >X-WBNR-Posting-Host: 84.147.129.198
> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >Subject: RE: Dumb question about SSIS
> >Date: Tue, 29 Nov 2005 03:38:03 -0800
> >Lines: 159
> >Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
> >MIME-Version: 1.0
> >Content-Type: text/plain;
> > charset="Utf-8"
> >Content-Transfer-Encoding: 7bit
> >X-Newsreader: Microsoft CDO for Windows 2000
> >Content-Class: urn:content-classes:message
> >Importance: normal
> >Priority: normal
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Both encryption settings were set to No.
> >Also, SQL Server 2005 is a named instance. The default instance is SQL
> >Server 2000.
> >In the "Connect to Server" dialog of management studio, I connect to
> >Integration Service, and as server name, the name of the machine. Does
> that
> >mean that I'm attempting to connect to the default instance? That would be
> >SQL 2000.
> >Does that mean that you cannot use SSIS on a machine where there is
> default
> >instance of SQL 2000'
> >
> >
> >"Peter Yang [MSFT]" wrote:
> >
> >> Hello,
> >>
> >> To understand the issue better, will you confirm the default instance is
> >> SQL server 2005 instance? SSIS does not support named instance anyway.
> >>
> >> Also, from the error message, it seems that client force encryption but
> >> server does not have certificate installed. Please check the following
> >> settings:
> >>
> >> Run SQL Server 2005 Network Configuration, right click SQL native client
> >> network conigurations->Properties, and configure "force protocol
> >> encryption" to NO.
> >>
> >> Right click the protocol under server network configuration and also
> >> configure "force protocol encryption" to NO.
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner Support
> >>
> >> When responding to posts, please "Reply to Group" via your newsreader so
> >> that others may learn and benefit from your issue.
> >>
> >> =====================================================> >>
> >>
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >>
> >>
> >> --
> >> >Thread-Topic: Dumb question about SSIS
> >> >thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==> >> >X-WBNR-Posting-Host: 84.147.181.254
> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> >> >Subject: RE: Dumb question about SSIS
> >> >Date: Mon, 28 Nov 2005 04:31:02 -0800
> >> >Lines: 76
> >> >Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> >> >MIME-Version: 1.0
> >> >Content-Type: text/plain;
> >> > charset="Utf-8"
> >> >Content-Transfer-Encoding: 7bit
> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >Content-Class: urn:content-classes:message
> >> >Importance: normal
> >> >Priority: normal
> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >Thanks Peter, exactly what I was looking for.
> >> >But now I still cannot see my package, as I'm getting following error
> >> >message when clicking on MSDB under "Stored Packages" in the Object
> >> Explorer
> >> >tree. Any idea why?
> >> >
> >> >Client unable to establish connection
> >> >Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> >> >
> >> > at
> >>
> >Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
> >> eInfos(String bstrPackageFolder, String bstrServerName)
> >> > at
> >>
> >Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
> >>
> >> >sPackageFolder, String sServerName)
> >> >
> >> >"Peter Yang [MSFT]" wrote:
> >> >
> >> >> Hello,
> >> >>
> >> >> You could click "Connect" button in Server management studio and
> click
> >> to
> >> >> select "Integration services", and then type the instance information
> >> you
> >> >> want.
> >> >>
> >> >> Hope this is helpful.
> >> >>
> >> >>
> >> >> Best Regards,
> >> >>
> >> >> Peter Yang
> >> >> MCSE2000/2003, MCSA, MCDBA
> >> >> Microsoft Online Partner Support
> >> >>
> >> >> When responding to posts, please "Reply to Group" via your newsreader
> so
> >> >> that others may learn and benefit from your issue.
> >> >>
> >> >> =====================================================> >> >>
> >> >>
> >> >>
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> >>
> >> >>
> >> >> --
> >> >> >Thread-Topic: Dumb question about SSIS
> >> >> >thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==> >> >> >X-WBNR-Posting-Host: 84.56.32.28
> >> >> >From: "=?Utf-8?B?SmFtZXM=?=" <aa1948@.nospam.nospam>
> >> >> >Subject: Dumb question about SSIS
> >> >> >Date: Fri, 25 Nov 2005 10:56:02 -0800
> >> >> >Lines: 7
> >> >> >Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
> >> >> >MIME-Version: 1.0
> >> >> >Content-Type: text/plain;
> >> >> > charset="Utf-8"
> >> >> >Content-Transfer-Encoding: 7bit
> >> >> >X-Newsreader: Microsoft CDO for Windows 2000
> >> >> >Content-Class: urn:content-classes:message
> >> >> >Importance: normal
> >> >> >Priority: normal
> >> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >> >NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> >> >Path:
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> >> >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
> >> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >> >
> >> >> >I just imported data from one database to another, I specified to
> save
> >> the
> >> >> >SSIS package on the database server (not on the file system).
> >> >> >Now I cannot find the package anywhere. There isn't a "Local
> Package"
> >> tree
> >> >> >like in 2000 Enterprise Manager. I can't even find a shortcut for
> >> starting
> >> >> >SSIS in my Start menu, and the SSIS Tutorial only shows how to
> create a
> >> >> new
> >> >> >project in VS, not how to open an existing package.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >>
> >>
> >
>

Dumb question about SSIS

I just imported data from one database to another, I specified to save the
SSIS package on the database server (not on the file system).
Now I cannot find the package anywhere. There isn't a "Local Package" tree
like in 2000 Enterprise Manager. I can't even find a shortcut for starting
SSIS in my Start menu, and the SSIS Tutorial only shows how to create a new
project in VS, not how to open an existing package.Hello,
You could click "Connect" button in Server management studio and click to
select "Integration services", and then type the instance information you
want.
Hope this is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcXx8eErD985vRkIQ9eWYJ2xuQK32Q==
>X-WBNR-Posting-Host: 84.56.32.28
>From: "examnotes" <aa1948@.nospam.nospam>
>Subject: Dumb question about SSIS
>Date: Fri, 25 Nov 2005 10:56:02 -0800
>Lines: 7
>Message-ID: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412281
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I just imported data from one database to another, I specified to save the
>SSIS package on the database server (not on the file system).
>Now I cannot find the package anywhere. There isn't a "Local Package" tree
>like in 2000 Enterprise Manager. I can't even find a shortcut for starting
>SSIS in my Start menu, and the SSIS Tutorial only shows how to create a
new
>project in VS, not how to open an existing package.
>|||Thanks Peter, exactly what I was looking for.
But now I still cannot see my package, as I'm getting following error
message when clicking on MSDB under "Stored Packages" in the Object Explorer
tree. Any idea why?
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
at
Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackage
Infos(String bstrPackageFolder, String bstrServerName)
at
Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
sPackageFolder, String sServerName)
"Peter Yang [MSFT]" wrote:

> Hello,
> You could click "Connect" button in Server management studio and click to
> select "Integration services", and then type the instance information you
> want.
> Hope this is helpful.
>
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> new
>|||Hello,
To understand the issue better, will you confirm the default instance is
SQL server 2005 instance? SSIS does not support named instance anyway.
Also, from the error message, it seems that client force encryption but
server does not have certificate installed. Please check the following
settings:
Run SQL Server 2005 Network Configuration, right click SQL native client
network conigurations->Properties, and configure "force protocol
encryption" to NO.
Right click the protocol under server network configuration and also
configure "force protocol encryption" to NO.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX0F5fDrM411FMOTvOM/FE4ub5KUA==
>X-WBNR-Posting-Host: 84.147.181.254
>From: "examnotes" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Mon, 28 Nov 2005 04:31:02 -0800
>Lines: 76
>Message-ID: <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412425
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks Peter, exactly what I was looking for.
>But now I still cannot see my package, as I'm getting following error
>message when clicking on MSDB under "Stored Packages" in the Object
Explorer
>tree. Any idea why?
>Client unable to establish connection
>Encryption not supported on SQL Server. (Microsoft SQL Native Client)
> at
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
eInfos(String bstrPackageFolder, String bstrServerName)
> at
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String

>sPackageFolder, String sServerName)
>"Peter Yang [MSFT]" wrote:
>
to[vbcol=seagreen]
you[vbcol=seagreen]
rights.[vbcol=seagreen]
the[vbcol=seagreen]
tree[vbcol=seagreen]
starting[vbcol=seagreen]
>|||Both encryption settings were set to No.
Also, SQL Server 2005 is a named instance. The default instance is SQL
Server 2000.
In the "Connect to Server" dialog of management studio, I connect to
Integration Service, and as server name, the name of the machine. Does that
mean that I'm attempting to connect to the default instance? That would be
SQL 2000.
Does that mean that you cannot use SSIS on a machine where there is default
instance of SQL 2000'
"Peter Yang [MSFT]" wrote:

> Hello,
> To understand the issue better, will you confirm the default instance is
> SQL server 2005 instance? SSIS does not support named instance anyway.
> Also, from the error message, it seems that client force encryption but
> server does not have certificate installed. Please check the following
> settings:
> Run SQL Server 2005 Network Configuration, right click SQL native client
> network conigurations->Properties, and configure "force protocol
> encryption" to NO.
> Right click the protocol under server network configuration and also
> configure "force protocol encryption" to NO.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> Explorer
> eInfos(String bstrPackageFolder, String bstrServerName)
>
> to
> you
> rights.
> the
> tree
> starting
>|||Hello,
You could still store SSIS package in filesystem other than msdb under this
situation. Since SSIS will try to access msde of default instance, you
could not store the package in msdb because it is a 2000 database.
Also, you may try the following method to change the instance SSIS
connects. However, it is not officially documented and may have issues when
running package from there
1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn
2. Change the string <ServerName>.</ServerName>?as following:
<ServerName>MachineName\InstanceName</ServerName>
3. Save the file and Re-Start SSIS Service
Please let me know if you have further concerns or questions on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Dumb question about SSIS
>thread-index: AcX02Vvn5mIYncaZQVq2jfNzlN2UsA==
>X-WBNR-Posting-Host: 84.147.129.198
>From: "examnotes" <aa1948@.nospam.nospam>
>References: <E17165D6-8E0C-40F9-982B-9A4530B8CF5A@.microsoft.com>
<hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
<36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
<yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Dumb question about SSIS
>Date: Tue, 29 Nov 2005 03:38:03 -0800
>Lines: 159
>Message-ID: <43861D30-F658-4906-B197-CE2D9DE8D97C@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.server:412564
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Both encryption settings were set to No.
>Also, SQL Server 2005 is a named instance. The default instance is SQL
>Server 2000.
>In the "Connect to Server" dialog of management studio, I connect to
>Integration Service, and as server name, the name of the machine. Does
that
>mean that I'm attempting to connect to the default instance? That would be
>SQL 2000.
>Does that mean that you cannot use SSIS on a machine where there is
default
>instance of SQL 2000'
>
>"Peter Yang [MSFT]" wrote:
>
rights.[vbcol=seagreen]
>Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetDtsServerPackag
>Microsoft.SqlServer.Dts.Runtime.Application.GetDtsServerPackageInfos(String
click[vbcol=seagreen]
so[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl[vbcol=seagreen]
save[vbcol=seagreen]
Package"[vbcol=seagreen]
create a[vbcol=seagreen]
>|||This sounds like a solution. Many thanks.
"Peter Yang [MSFT]" wrote:

> Hello,
> You could still store SSIS package in filesystem other than msdb under thi
s
> situation. Since SSIS will try to access msde of default instance, you
> could not store the package in msdb because it is a 2000 database.
> Also, you may try the following method to change the instance SSIS
> connects. However, it is not officially documented and may have issues whe
n
> running package from there
> 1. Edit the Configuration file MsDtsSrvr.ini.xml in the following path:
> C:\Program Files\Microsoft SQL Server\90\DTS\Binn
> 2. Change the string <ServerName>.</ServerName>?as following:
> <ServerName>MachineName\InstanceName</ServerName>
> 3. Save the file and Re-Start SSIS Service
> Please let me know if you have further concerns or questions on this.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> <hsnnFv88FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> <36EAC47F-E63C-4DA8-9F75-ED51B80E761B@.microsoft.com>
> <yI1K45L9FHA.4000@.TK2MSFTNGXA02.phx.gbl>
> that
> default
> rights.
> click
> so
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> save
> Package"
> create a
>

dtUtil question - How to include miscellaneous files in deployment?

Hello,

I'm wondering how to use the dtUtil command to deploy an ssis package but also include any of the miscellaneous files, such as some dtsconfig files. I'd like to be able to use the dtUtil command to specify where to put any "dependencies" that I have. If I create a deployment utility from BI Studio, I have the option on the third screen to choose the folder for dependencies, how come I don't see any parameters for that on the dtUtil command? Is it possible, or do I HAVE to deploy using the deployment utility created for me by BI Studio?

Thanks in advance,

Andy

When deploying to a file system /copy option should work for dependencies as well.

For example:

dtutil /File c:\2.reg /copy File;c:\3.reg

|||[quote]
dtutil /File c:\2.reg /copy File;c:\3.reg
[/quote]
That's very interesting; as long as you are 'deploying' to the same machine. Maybe it's just me, but 'deploying' to the same machine isn't really 'deployment' - normally you need to do it to a remote machine; and if you try to specify a /destserver, you get....

The File destination cannot be specified with the DTS, SQL, Server, User, or Password destinations.

dtsx with config file?

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!

There are a number of ways of doing this. You could use a configuration file but that would mean editing the configuration file each time and I don't think that's what you want to do.

You could change the value using the /SET option of dtexec.exe. Will that work for you?

-Jamie

DTSX package will not run from SQL server agent

When I try to run a DTSX package from SQL server agent, I get this error message:

Started: 3:59:42 PM Error: 2007-08-13 15:59:42.39 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSroperty" 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: 2007-08-13 16:00:00.98 Code: 0xC001602A Source: CFASerialNoExport Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-08-13 16:00:00.98 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The packag... The package execution fa... The step failed.

All my other packages run fine. This one contains an FTP task and will not run, although it will run if I execute it through the visual studio.

May be package is set with encryption, save the package with encryption level "Dont Save Sensitive" (if you are using configuration file) or alternatively you can set the encryption level to "rely on server storage for access and roles" in sql server when importing the package.

Thanks

|||

See if this KB article helps:

http://support.microsoft.com/kb/918760

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS
'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS
' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].& #91;ASP_CCTS_RunImportContractorEmployee
_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS
'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:

> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS
'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS
' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].& #91;ASP_CCTS_RunImportContractorEmployee
_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS
'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:
> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTSX package data transfer error

I will try to explain things the best I can.

When the data is transferred from source to destination (replace not append), the data in one field in one table is incorrect. Both source and destination tables have the same number of rows (8493). The ProductID field data range at the source is from 58958 to 73008. When the table is copied the ProductID field data runs from 1 to 8493.

What would cause this skewing of data?

This happens on brand new dtsx packages and this only happens in one field out of 5 different tables.

I am baffled. Any help is appreciated.

Thanks.

Andrew

Sounds like ProductID is an identity field.

In the OLE DB destination, ensure that you are using a data access mode of "Table or view - fast load" and then make sure the check box is selected for "Keep identity."

See if that helps.|||

Thanks for the quick response Phil.

Where exactly do I look for this setting in Visual Studio?

I must be overlooking it.

Thanks.

Andrew

|||It's in the OLE DB Destination component. Double click on it.|||Did you develop the package, or use the Import/Export wizard?|||I devolped it.|||

FordyH500HP wrote:

I devolped it.

Then what destination are you using? Use the OLE DB Destination if you're not using it already.|||

I'm using the Transfer SQL Server Objects Task from the toolbox.

Will that work for what you are talking about?

|||

FordyH500HP wrote:

I'm using the Transfer SQL Server Objects Task from the toolbar.

Will that work for what you are talking about?

Ahh... What values for the options under "Table Options" do you have?|||

Sorry if I should have stated that sooner.

I'm just above Newbie status in Visual Studio.

CopyIndexes - True

CopyTriggers - False

CopyFullTextIndexes - False

CopyPrimaryKeys - False

CopyForeignKeys - False

GenerateScriptsInUnicode - False

|||What happens if you set CopyPrimaryKeys and CopyForeignKeys to True?|||Same result. It renumbers the ProductID field starting with 1.|||

FordyH500HP wrote:

Same result. It renumbers the ProductID field starting with 1.

Yep, doing a quick search shows that the Transfer SQL Server Objects task does not support identity columns. Will future versions?

Let's find out:
[Microsoft follow-up]

You can do this on your own though, by using an execute sql task in the control flow to truncate the destination tables and then as many data flows as you have tables. Inside the data flows, you can use an OLE DB source and hook it up to an OLE DB destination, and use the options I suggested above.|||

Ok. I think I understand.

So I want to check Keep Identity then in the data flow destination?

Also, can I put multiple Source/Destinations in one data flow?

|||Yep

DTSX package continues to throw errors when working with large dataset.

I have a dataset that is between 40-50K records that has to go through a process that is pre-defined. SSIS works just fine with the smaller sets even up to 20K but this job keeps blowing up saying something along the lines of cannot write to recordset destination. Does this make sense to anyone? The sever is a 2 processor with 2GB of ram. Physical memory usage spikes to about 1.6GB during the run but the processor never really gets above 30% usage. Does this product just not scale yet?
I'd be interested to see the full error message. Depending on how wide the buffer is, trying to stuff that many rows into a variable may be a bit optimistic, just because of the memory requirements. The SSIS pipeline is great for large volumes, and the buffer design means you don't have to load all data into memory at once, but I suspect you are actually forcing just that by using the Recordset Destination. Why do you want to do this?

DTSX package calling another DTSX package question

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?

If you are using an Execute Package Task in the first package to execute the second package, you could change the constraint value on the precedence constraint connecting the execute package task to the task(s) after that to "completion".

Hope this helps..

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Bhargavi Srivathsan wrote:

If you are using an Execute Package Task in the first package to execute the second package, you could change the constraint value on the precedence constraint connecting the execute package task to the task(s) after that to "completion".

Hope this helps..

---

This posting is provided "AS IS" with no warranties, and confers no rights.

And you'll need to either force the execute package task to return a success, or set the MaximumErrorCount to something greater than 1 (one) because when the child package errors out, it's failure will bubble up to the parent and stop it as well.|||however, i also do want to know if my child failed out..so i force success then i would never know..same with increasing maxerrorcount. any other way?

dtsx Fuzzly Grouping Package

I have SQL 2005 Developer edition and wanted to play with the Fuzzy Grouping featutre. I have created a package where I have a datareader source, fuzzy grouping, and SQL server destination. When I try to run this package I get the errors:
Error: The product level is insufficient for component "Datareader Source" [1]
Error: The product level is insufficient for component "Fuzzy grouping" [12]
Error: The product level is insufficient for component "SQL Server destination" [712]

I do get other warning before that about performance but these are the only errors. I increased the error level but that does not help, the above errors make the task fail.

What does it mean by "Product Level" and what am I doing wrong?

I can email code if necessary.

Thanks

DeanWhich release are you using?|||

When I do the help about I see:

MS visual studio 2005 version 8.0.50215.44
.Net framework version 2.0.50215
Microsoft SQL Server Analysis Services Designer
Microsoft SQL Server Analysis Services Designer
Version 9.00.1187.00

Microsoft SQL Server Integration Services Designer
Microsoft SQL Server Integration Services Designer
Version 9.00.1187.00

Microsoft SQL Server Report Designer
Microsoft SQL Server Report Designer
Version 9.00.1187.00

Microsoft SQL Server Report Model Designer
Microsoft SQL Server Report Model Designer
Version 9.00.1187.00
Does this help?

|||You're using developer edition right? Those components that are listed only work on Standard or Developer edition.

This link exlains what each edition contains: http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx

Actually it doesn't mention DataReader Source or SQL Server Destination but near the bottom you'll see clearly that fuzzy stuff is only in Enterprise Edition.

-Jamie|||Jamie,

Before I tackle the couple hours of uninstalling, downloading and reinstalling I just want to make sure that you are certain. On the page you specified it does not show the developer edition in the matrix and on this page http://www.microsoft.com/sql/2005/productinfo/ctp.mspx it specifically says that the developer edition includes all the functionality of the enterprise edition but is only licensed for developement and test systems. Also why would the controls and everything be in the developer edition if you could not use them. The error that I got makes me believe you may be right but all the documentation says differently.

Are you 100% positive?|||I'll be honest and say I was making an assumption based on the errors that you were getting. So 90% positive :)|||It sounds like you've got a configuration problem; all the components should work on the Developer Edition. What else do you have installed on that box? Have any other editions of SQL Server 2005 ever een installed on the machine? Have any verions of Visual Studio? (If so, did you install the edition of SQL Server that is selected by default during the Visual Studio installation?) Have you previously had other installations of SQL Server on the machine? What about SQL Server 2000?

DTSTransformationSTATus constants !

Hi
I am building a DTS package, to read a IIS Log files into SQL-Server database file through a DTS package.

It is runing smooth, but sometimes the Log file include some CR LF, unexpected text,...etc so the DTS package can't read the fields perfectly and it fails, and I recieve an error.

I am up to use ActiveX Script in VBScript language to have more control over the flow of the transformation of the data operation.

Now, how can I use the DTSTransformationSTATUS constants to make my loops and check the different source fields?

I will appreciate an examples that control the folow of the Transformation of a TEXT file.

Thank you for your help in advance.

KhalidHow big are the log files ? What are the patterns to these hiccups ? Have you thought about writing a small app(vb,perl) that corrects the file first then use dts to import the file - you could actually make this a part of your dts package (calling the app) ?|||The log files are 50 MB to 60 MB in size.

About writing an Application to correct the log file, this is exactuly what I am up to, and since DTS is providing a powerful tools through VBScript or VBJScript to have more control over the Transformation process, I have decided to use these tools.

There are some constants such as DTSTransformationSTATUS_Error, DTSTransformationSTATUS_ErrorInfo,.. etc . These constants can be used to control the flow of the Transformation process and to handle the errors if any.

I need an example using these constants to make things more clear for me to build my own application.

Do you have any idea about these constants?

Thanks

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTSTransform.DataConvert programmatic access?

I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.

The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.

I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.

I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?

Here is what is left of my code:

IDTSComponentMetaData90 convert= dataFlow.ComponentMetaDataCollection.New();
convert.ComponentClassID = "DTSTransform.DataConvert";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper instance = convert.Instantiate();
instance.ProvideComponentProperties();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], onvComponent.InputCollection[0]);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Iterate through the inputs of the component.
IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY);
//instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1);
}

Did you find a resolution to this issue? I am struggling through the same thing and have seen no example usage of the data conversion transformation anywhere.

Phil Burns

Aptify

DTSRun.exe doesn't exit if i run from command prompt

Hi,
I have a DTS package. When I run it from a DOS prompt as following,
c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name
Eveything is fine. I can see something like log messages output from the
command.
Then if I redirect output into a file like and run it again,
c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name >output_file
then the command never come back from it running. I'm sure the dts package
itself has finished running by check the output_file. The output_file said
like that the package has completed.
Can any help me solve this issue, what is the problem?
-Senthil
Maybe
WWW.SQLDTS.COM
Saludos
IIslas
"Spsk" wrote:

> Hi,
> I have a DTS package. When I run it from a DOS prompt as following,
> c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name
> Eveything is fine. I can see something like log messages output from the
> command.
> Then if I redirect output into a file like and run it again,
> c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name >output_file
> then the command never come back from it running. I'm sure the dts package
> itself has finished running by check the output_file. The output_file said
> like that the package has completed.
> Can any help me solve this issue, what is the problem?
> -Senthil
|||Thanks, I couldn't find any related this issue the sqldts.com...
Could anyone help me..
-Spsk
"Isaias" wrote:
[vbcol=seagreen]
> Maybe
> WWW.SQLDTS.COM
> --
> Saludos
> IIslas
>
> "Spsk" wrote:

DTSRun.exe doesn't exit if i run from command prompt

Hi,
I have a DTS package. When I run it from a DOS prompt as following,
c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name
Eveything is fine. I can see something like log messages output from the
command.
Then if I redirect output into a file like and run it again,
c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name >output_file
then the command never come back from it running. I'm sure the dts package
itself has finished running by check the output_file. The output_file said
like that the package has completed.
Can any help me solve this issue, what is the problem?
-SenthilMaybe
WWW.SQLDTS.COM
--
Saludos
IIslas
"Spsk" wrote:

> Hi,
> I have a DTS package. When I run it from a DOS prompt as following,
> c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name
> Eveything is fine. I can see something like log messages output from the
> command.
> Then if I redirect output into a file like and run it again,
> c:>dtsrun /S sql_server /U userid /P pwd /N dts_package_name >output_file
> then the command never come back from it running. I'm sure the dts package
> itself has finished running by check the output_file. The output_file said
> like that the package has completed.
> Can any help me solve this issue, what is the problem?
> -Senthil|||Thanks, I couldn't find any related this issue the sqldts.com...
Could anyone help me..
-Spsk
"Isaias" wrote:
[vbcol=seagreen]
> Maybe
> WWW.SQLDTS.COM
> --
> Saludos
> IIslas
>
> "Spsk" wrote:
>