Migrating DTS Active X/ADO Script To SSIS
Aug 1, 2006
Hi All,
In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:
Retrieve a list of account numbers through Recordset1
While Not Recordset1.eof
Build a sql statement based on fields from current row of Recordset1
Open Recordset2 based on the sql statement built
Run an update query based on results from recordset 2
Loop
I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.
Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?
Thanks in advance
Jeff
View 1 Replies
ADVERTISEMENT
Sep 20, 2004
A little background, We have a DEV Server running SQL Server 2000. This is the first of many to be migrated from out NT Domain to our new AD (active directory Domain). All Domain user accounts have already been migrated.
When they migrated this first Server running SQL Server, I am getting the following error when I try to make the owner of a job (any job) run by the SQL Server Agent a domain account in the new AD - when I switch the ownership back to our old NT Domain, it works fine.
I am getting this error:
The job failed. Unable to determine if the owner (domainusername) of job testjob has server access (reason: Could not obtain information about Windows NT group/user 'domainusername'. [SQLSTATE 42000] (Error 8198)).
note that this is happening to all windows authenticated sql server accounts on this Server. All of these account are in the local Admin group on the Server.
Does anyone know what needs to be done in SQL Server to make the AD migration seemless???? I need to try and find this out before we begin migrating Production Servers. Thank you!!
View 6 Replies
View Related
Feb 7, 2008
I have lots of DTS packages and jobs in SQL 2K, we are planning to migrate to SQL 2005. Will my DTS and jobs still work? If not, what are the procedures for migration. thanks
View 3 Replies
View Related
Jan 31, 2008
I am tasked with migrating a few DTS packages to SSIS from 32bit 2000 box to 32 bit 2k5 box. These DTS packages contain simple processing of AS cubes. When I run the wizard to migrate the package on my sql 2k5 box, I get the following error during processing:
Could not load file or assembly "Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
and the wizard shows the status being "Stopped". I found the Microsoft.SqlServer.Exec80PackageTask.dll on a 64 bit server located in C:Program Files (x86)Microsoft SQL Server90DTSTasks and copied it to the 32 bit 2k5 box. This still did not work and the error log produces
...........
LogID=4
#Time=9:53 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message= at Microsoft.SqlServer.Dts.MigrationWizard.TasksMigrationModules.CustomTaskMigrationModule.Migrate(Task task, Package& yukonPackage)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)
...........
LogID=6
#Time=9:53 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.MigrationWizard.HelperUtility.DTSMWException: Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at Microsoft.SqlServer.Dts.MigrationWizard.TasksMigrationModules.CustomTaskMigrationModule.Migrate(Task task, Package& yukonPackage)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.MigrateTasks(DTS8Package shilohPackage, Package& yukonPackage)
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLMSoftwareMicrosoftFusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLMSoftwareMicrosoftFusion!EnableLog].
I also tried re-installing the backward compatability components to no avail. Any ideas?
View 13 Replies
View Related
Aug 17, 2007
Hi ,
Getting error even remove Text (Source) task in DTS.
Pl. help me .
Thanks,
Raja
Error:
LogID=23
#Time=11:32 AM
#Level=DTSMW_LOGLEVEL_ERR
#Source=Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework
#Message=Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Failed to save package file "C:PONE_SSIS_PACKAGESPONE_SSIS_SELLTHRUPONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".
---> System.Runtime.InteropServices.COMException (0xC001100E): Failed to save package file "C:PONE_SSIS_PACKAGESPONE_SSIS_SELLTHRUPONE_DTS_SELLTHRU_TEST.dtsx" with error 0x80004005 "Unspecified error".
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.SaveToXML(String FileName, IDTSPersist90 pPersistObj, IDTSEvents90 pEvents)
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Dts.Runtime.Application.SaveToXml(String fileName, Package package, IDTSEvents events)
at Microsoft.SqlServer.Dts.MigrationWizard.DTS9HelperUtility.DTS9Helper.SaveToXML(Package pkg, String sFileLocation)
at Microsoft.SqlServer.Dts.MigrationWizard.Framework.Framework.StartMigration(PackageInfo pInfo)
View 9 Replies
View Related
May 29, 2008
Hi all - just wondering if anyone could give me an idea of how arduous this task would be (migrating Cognos Data Manager scripts to SSIS). I realize it wont be a trivial endeavour, but wondered how much of a headstart having the initial validated Cognos catalog would be to this project (or would it be pretty much useless?)
Thanks
View 4 Replies
View Related
Oct 3, 2007
I have created a SSIS package to load data into a destination table.
Now I cheked that package into main server. Then I tried to change path for both source and destination connection managers. I'm not sure whether I can do that.
But now what I want is load data into a data base which is in another server (Perforce).
Can I do this? Or Am I doing smthing wrong?
If so, Which is the correct way?
View 5 Replies
View Related
Nov 29, 2007
I have migrated DTS to SSIS through migration utility. The origional DTS has lots and lots of VbScript.
SSIS has converted it into 8 to 10 Activex script task. When I run the package it gives error in the first activex task.
I found on net the SSIS does not understand Vbcript and this needs to convert into .Net.
Please guide.
View 1 Replies
View Related
May 27, 2008
Hi all,
I need to migrate some documents(in GB's) from FTP Server1 with meta data information in SQLdbA to FTP Server2 with meta data information in SQLdbB. How can we achieve this?
Am new to this concept and got information that we can use FTP task. But unable to proceed how to achieve this. Please help me.
View 2 Replies
View Related
Mar 11, 2008
Hello,
Is it possbile to migrate SS2000 DTS packages to SS2005 SSIS involving databases with compatibility mode set to 80. If yes, what are the potential issues, which may occur?
Sajish
View 6 Replies
View Related
Oct 10, 2007
Hi All,
Can anyone suggest me a way to migrate data from paradox tables to sql server tables using SSIS.
Paradox tables are having millions of rows.
Any help on this is greatly appreciated.
Thanks,
SVGP
View 8 Replies
View Related
Apr 6, 2006
Hi,
I have created a simple SSIS package in a 32-bit server and I'm able to execute it using dtexec command in the same server.I copied this package to the 64-bit machine and when try to execute the pacakge using dtexec /f <package path> ,it is giving me the following error
"The connection manager "10.101.24.230.master 1" will not acquire
a connection because the package OfflineMode property is TRUE. When the Offline
Mode is TRUE, connections cannot be acquired."
But in SSIS designer OfflineMode property for the package is by default set only to false but still I get this error in 64-bit.
Any help in this regard would be appreciated.
Thanks
SG
View 1 Replies
View Related
Jan 3, 2008
Hi,
In my script 'm using
Set oPkg = DTSGlobalVariables.Parent
Set oBeginStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_1")
I guess becoz of using DTSGlobalVariables.Parent in activex,the script is not working.
Can anyone please suggest a solution to run it in SSIS.
Thanks in Advance
View 3 Replies
View Related
Apr 10, 2006
I have migrated several (7) DTS packages to SSIS in a development environment. In this environment these packages all work as designed. I am now in the process of placing the .dtsx files on the production server and scheduling jobs around them.
I am encountering problems in 3 of these 7 packages. I get the following error:
Message
The job failed. The Job was invoked by User <domainuser>. The last step to run was step 1 (Create and Transfer Credentialling Data).
This is not what I would call very informative. I have opened the dtsx files in question in BIDS on the production server and each has the following error reported:
Error loading <name>dtsx; Failed to decrypt protected XML node "PassWord" 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.
I have looked at the package setting "Protection Level" and all seven packages have the same setting: EncryptSensisitveWithUserKey
Each of the connections within the packages have been defined to utilize Windows Authentictation. And the jobs are set to run under the SQL Agent Account.
What I find most puzzeling is that each of these packages are defined exactly the same and some work correctly and others do not.
Thanks for taking the time help me with this issue.
View 5 Replies
View Related
Mar 3, 2006
Hi,
We need to migrate data from Sqlserver2000 database to Sqlserver2005. The SQLserver2000 DB was poorly Normalized and the DB was redesigned inSqlserver 2005. Both the databases are operational.Currently we have around 90 tables that need to migrate the data from the OLD DB to NEW DB, all the table mappings between the old schema and new schema have been successfully established.
Each Table is treated on its own merit and applying its own business rules which allows the data to be transformed to the new tables.The client wants us to build a SSIS solution that needs to performs the following
a. Import all Look Tables.
b. Import all master Tables ( One time import of some tables data and other tables may need to import every 4 hrs)
c. Import all Transaction tables( One time import and other tables may need to import every 4 hrs of data)
My Question is how to apporoach this scenario of pulling one time lookup, master and transaction tables and scheduling 4 hrs data importing process, how to build a SSIS solution for this type of scenario.
Thanks
Jayadev
View 4 Replies
View Related
May 13, 2008
I have successfully used migrate wizard to migrate DTS pacakge to TrainingDTS.dtsx.
What should I do next? when I run
C:>dtexec /file "C:TrainingDTS.dtsx"
Error: 2008-05-13 09:14:31.36
Code: 0xC0029172
Source: File Transfer Protocol Task undefined FTP Task
Description: The connection is empty. Verify that a valid FTP connection is p
rovided.
End Error
Error: 2008-05-13 09:14:31.36
Code: 0xC0024107
Source: File Transfer Protocol Task undefined
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
View 2 Replies
View Related
Apr 4, 2006
Hi,
I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,
- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables
Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow
-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )
-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client
pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not
Thanks for ur help
Niru
View 2 Replies
View Related
May 12, 2006
I'm trying to query against AD to grab some data. I've done this setup and got it to work at my location but can't get it working at one of my customers.
Per information I've found via this site I did the following:
Task: from SS2005, select data elements from Active Directory and populate in ODS (using an
SS2005 Package in SSIS)
I created a linked server on the MS2005
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
I then created the following View (in the Master DB):
CREATE VIEW viewADContacts
AS
SELECT [NAME],MAIL
FROM OPENQUERY( ADSI,
'SELECT NAME, MAIL
FROM ''LDAP://#######/ DC=####,DC=###''
')
The View created w/ no errors
When I execute
select * from viewADContacts
I get this error:
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".
Any thoughts on this error? Again, I did the exact same thing at my office (against my local AD) and it worked fine.
Thanks in advance
Tom
View 3 Replies
View Related
May 22, 2008
I hope this topic is appropriate for this forum. If not, I pre-apologize.
I have an SSIS package that takes data from active directory and dumps it to a table.
Here is the query I am using:
SELECT displayName, Mail, Title, physicalDeliveryOfficeName, description, telephoneNumber, department
From 'LDAP://ourserver'
WHERE objectClass='user' AND objectCategory='Person'
I am dumping it into a table on SQL Server where the columns are all of data type: NTEXT.
All columns come across GREAT except the column DESCRIPTION. We keep a piece of information in that column that is important and without it the SSIS is worthless. What is coming across in that column is the following:
System.Object[]
No values are coming across. It should be character data up to 10 in length.
Anyone see this or know of something that I can do to force the "real" data to come through?
Thank you in advance for any consideration you give.
James
jamestow@yahoo.com
View 16 Replies
View Related
Feb 1, 2008
Hi
I have migrated a DTS that had some activeX transformation tasks within data pump flow tasks.
Those parts were migrated as "DTS 2000 tasks" .. so activeX transformation tasks aren't possible in SSIS ?
I know ActiveX script tasks are but for transformations ?
1. IF i leave these Encapsulated DTS 2000 tasks in the migrated SSIS package, will it run independently of the original
DTS or does it need the old DTS running to "call" that part from ? (I hope im making sense here)
is it possible to load this functionality internally into the new SSIS ?
2.
How could I (if i can't do ActiveX transformation tasks) achieve this is SSIS ?
can I achive this using the script tasks in SSIS ?
View 6 Replies
View Related
Feb 13, 2008
Below is a migration plan that I've compiled to migrate SQL 2000 DTS packages to SSIS 2005. Once these DTS packages have been migrated i will need to create a job and schedule them in SQL 2005.
I would appreciate and feedback or questions on this migration plan.
Migration DTS 2000 packages to SSIS 2005:
1. Will need to save the current production DTS package as structure storage file. We do not have a UDL file. We set the data connections within each DTS package.
2. Go to Sql 2005 - ManagementLegacyData Transformation Services - right-click and open previous saved structure storage file.
3. Modify the DTS data creditentials to reflect the SQL 2005 connection data. Modify any SQL 2000 MAPI settings to utilize SQL 2005 new database mail. Save the package on SQL 2005.
4. After the modified DTS package has been updated and saved on SQL 2005, save this file as a structure storage file.
5. go to BIDS. Create a new SSIS project. Right-click on SSIS packages and select Migrate DTS 2000 package. This will migrate over the DTS 2000 package with the updated SQL 2005 data creditentails.
6. click on the package properties - protectionlevel and change it to dontsavesensitive.
7. right-click and select package configurations..., select to store data creditentials in xml format.
8. right-click on execute DTS 2000 package task, select Edit... and click on Load DTS2000 package internally. This will embed this task into the new SSIS package. Test the package. Continue if successful.
9. Use SSIS deployment functionality to move the package over to SQL 2005 Integration Services. Right-click on package and select Run Package, if successful, create a job and schedule it to run on SQL 2005 Agent.
10. When creating the Job under SQL Agent, change the Owner: of the job to reflect the owner of the new SSIS package. Schedule the job.
I've just coming up to speed on SSIS 2005. Therefore, this is what i've been able to piece together up to this point and I'm looking for some industry advice/feedback on whether or not this is a good migration plan. I need to provide a migration plan to management by 2/18. Thanks
View 1 Replies
View Related
Oct 29, 2007
Hi - Im migrating packages in dts 2000 to SSIS 2005 using package migrator wizard. It loads a CSV to a table in DB. I need to change the server and file location paths after migration. While changing this, im encountering error - "the acquireconnection method call to connectionmanager failed
View 1 Replies
View Related
Apr 28, 2008
I applied the following info and created an SSIS package that allows COM control of Excel:
unable to add reference to Microsoft Excel in VSA
The package runs locally, and can be executed successfully after it is migrated to the server, but both of these processes use local memory space (I think). When I try to run the package via a job, it fails with the following error:
-------------------------------------------------------------------------------------------
Date 4/28/2008 10:34:04 AM
Log Job History (J_MSR_UHC_PC)
Step ID 1
Server SFA30229SQL003
Job Name J_MSR_UHC_PC
Step Name S_MSR_UHC_PC
Duration 00:00:05
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: ALEREsql_admin. ...on 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:34:04 AM Error: 2008-04-28 10:34:06.73 Code: 0xC001405F Source: Description: Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information. End Error Progress: 2008-04-28 10:34:09.34 Source: Data Flow Task Validating: 0% complete End Progress Error: 2008-04-28 10:34:09.45 Code: 0xC0202009 Source: msr_uhc_pc Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-04-28 10:34:09.45 Code: 0xC020801C Source: Data Flow Task... The package execution fa... The step failed.
----------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.dll has been added to the assembly directory.
Any ideas would be appreciated.
TruckeeBill
View 4 Replies
View Related
Mar 3, 2008
I'm migrating DTS containing a Loop that performs tasks again:
in the last task:
stpBegin = Pkg.Steps("DTSStep_DTSActiveScriptTask_1");
stpBegin.ExecutionStatus = DTSStepExecStat_Waiting;
My needs are:
1: exec SQL Task 1
2: Script task 1 (write log/insert into table)
3: exec SQL Task 2
4: script task 1 again
5: exec SQL Task 3
6: script task 1 again
etc... etc...
I've one only task for write loginsert into table. Can I switch it to "waiting" status and do it next step?
How can I do this?
View 3 Replies
View Related
Aug 14, 2007
I have a activeX script like below in DTS and I am trying to rewrite it in SSIS.
What is the best way to do this?
Can I do this using a look up table? or other transformers in SSIS
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then
DTSDestination("RACE_NATIVE_AM_IND") = "Y"
else
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "2" then
DTSDestination("RACE_NATIVE_AM_IND") = "N"
end if
end if
if DTSSource("RACE_ASIAN_YN") = "1" then
DTSDestination("RACE_ASIAN_IND") = "Y"
else
if DTSSource("RACE_ASIAN_YN") = "2" then
DTSDestination("RACE_ASIAN_IND") = "N"
end if
end if
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "1" then
DTSDestination("RACE_BLACK_IND") = "Y"
else
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "2" then
DTSDestination("RACE_BLACK_IND") = "N"
end if
end if
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then
DTSDestination("RACE_HAWAIIAN_IND") = "Y"
else
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "2" then
DTSDestination("RACE_HAWAIIAN_IND") = "N"
end if
end if
if DTSSource("RACE_CAUCASIAN_YN") = "1" then
DTSDestination("RACE_WHITE_IND") = "Y"
else
if DTSSource("RACE_CAUCASIAN_YN") = "2" then
DTSDestination("RACE_WHITE_IND") = "N"
end if
end if
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 9 then
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 40
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource ("RACE_ASIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 16
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource ("RACE_AFRICAN_AMERICAN_YN") = "1" then
DTSDestination ("RACE_CD") = 32
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 51
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource("RACE_CAUCASIAN_YN") = "1" then
DTSDestination("RACE_CD") = 31
DTSDestination("RACE_MULT_IND") = "N"
DTSDestination("RACE_OTH_IND") = "N"
end if
end if
end if
end if
end if
else
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 10 then
DTSDestination("RACE_CD") = 99
DTSDestination("RACE_MULT_IND") = "N"
DTSDestination("RACE_OTH_IND") = "N"
else
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) < 9 then
DTSDestination("RACE_CD") = 52
DTSDestination("RACE_MULT_IND") = "Y"
DTSDestination("RACE_OTH_IND") = "N"
end if
end if
end if
Main = DTSTransformStat_OK
End Function
View 3 Replies
View Related
May 20, 2008
Hi All
I am trying to run a query against our 'Active Directory' and I keep getting errors. I have done the following:
1. Set up Linked Server (using the OLE DB For Directory Services) and also set the login information:
EXEC sp_addlinkedsrvlogin 'ADSI', False, 'SQL_Usr','DDEMOJillSmith', 'PassWord'
2. Created a connection in SSIS
3. In My SSIS Package I have created a dataflow select the AD connection 'ADSI'.
4. Tried to run the following query:
SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
''LDAP://OU=Main_Users, DC=DemoAD,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''
And it fails - I can get this working in SQL 2000 DTS by using an Open Query statement!
The Error message I get when trying to test the query is:
Error at Export From AD [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
I have event tried the working OPEN QUERY in SSMS and I get the following:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,
department, title, sn, givenName, name, employeeNumber FROM
'LDAP://OU=DEMO_Users, DC=DEMO,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Am I missing something?
Thanks Gopher
View 4 Replies
View Related
May 29, 2015
How can I copy data like firstname, lastname, email from Active Directory into a SQL Server table?
View 4 Replies
View Related
Nov 1, 2006
Hi
I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.
Function Main()
Dim x
For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then
DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")
Else
DTSDestination(x) = DTSSource(x)
End If
Next
Main = DTSTransformStat_OK
End Function
Andre
View 8 Replies
View Related
Jan 7, 2008
Greetings,
I need to install the SSIS components on the referenced database server. I believe the SSIS components are cluster aware, so I was planning on just doing add/remove and selecting the components. Rather than breaking the cluster apart to patch the components back to SP1, I was thinking of just running the SP2 install right over the top of the running cluster to bring everything up to SP2.
I'm looking for any suggestions/comments on whether this is the proper way to proceed or if I'm going to have a problem.
TIA
View 1 Replies
View Related
Jan 22, 2015
I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
[code]....
can see how many sessions logged in per hour with the following statement:
SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASCResults:
opDateopHourTotal
2015-01-20041
[code]....
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
View 5 Replies
View Related
Mar 5, 2008
Hi
I am having some teething problems while installing SQL on a 3 node cluster. Within the Cluster configuration I have 3 Cluster Groups with each of them having their associated disk resources. All these disk resources physically exist on a SAN.
The actual cluster is running absolutely fine and I can access all the disks from their respective owner node. The problem only starts when I start installing SQL Server 2005 on this cluster. I specify the Cluster group from the Cluster Group Selection and choose the desired partition and then the error message pops up
"There is not enough diskspace on the destination disk for the current SQL Server data files. To proceed, free up disk space to make room for data files, or install the data files to a different drive"
But the disk I am trying to install it on is 264Gb and none of it is used. I have also tried to change it to a different disk within the same Cluster group but to no avail. I have even tried to install it in a different cluster group all together but I get the same error message.
I have googled around havent found anything so far. The disks have got full permissions for the account I am installing SQL with.
Any help will be much appreciated.
Regards
Adnan
View 5 Replies
View Related
May 12, 2008
Hi
I am newbie in SQL Clustering. I have set up a Windows Server Cluster with 2 nodes and am having the following problem with Physical Disk resource for cluster groups:
My Default Cluster Group (named Cluster Group) has IP Address, Network Name, Physical Disk and MSDTC resources. In addition to that my Default SQL Server instance resources are also in this group. I had this initial set up for Active/Passive mode.
Now I am trying to set up a SQL Cluster in Active/Active mode. For this I have to install another instance of SQL Server in the existing cluster and make a separate cluster group for its resources. I made a new cluster group (SQL Instance Group) with an IP Address and a Network Name resource for this new instance but I dont have any Physical Disk resource to allocate to it. As such while installing the SQL Server Instance I get stuck when I'm asked to select the quorum disk to be used.
Is it possible to configure two quorum disks, one for each group?
What's the concept of dedicated disks resource for each sql instance in a group? Is this same as the quorum disk? If this is not a shared disk how do I configure a dedicated disk resource for my second cluster group (SQL Instance Group)?
Anyone could please help me out with this?
View 12 Replies
View Related
Nov 30, 2006
Hi folks,
We are going thru the process of scoping an active/active cluster at one site.
I was wondering whether there will be any issues with mirroring (DB by DB) off the cluster into non clustered server at an alternate DRP site.
Regards, Brian
Sutherland
View 1 Replies
View Related
