Problem while importing active directory .csv file in to SQL 2005

Question

Post reply

Problem while importing active directory .csv file in to SQL 2005

Parthipan-244146

SSCrazy

Points: 2178
More actions
November 25, 2008 at 5:50 am

#383457

Hi All,
I imported all columns of active directory data in to a .csv file by
CSVDE -f E:\adcollection.csv
then when I tried importing through import data wizard of SQL 2005, the columns names(heading) were delimited properly but the data are not getting demilited properly.
The import wizard is working perfectly in SQL server 2000 by selecting appropriate code page, row delimiter, column delimiter.
I tried configuring the same code page, row delimiter, column demliter value like Sql 2000 the condition was same.
I also tried different code page, row delimiter, column delimiter value and also BULK Insert command and was not working.
For example : From the .csv file the value "DC=lab,DC=test,DC=aa" was imported correctly as col1 - DC=lab,DC=test,DC=aa in SQL 2000
But in sql 2005 the same was wrongly delimited as col1 - lab , col2 - test , col3 - aa
Has anybody tried importing all columns of AD data into SQL 2005? if so please suggest me how to troubleshoot it..
BR,
Parthi

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

edwardelliott SSCommitted Points: 1863 More actions · Answer 1

after you select "Flat File Source" have you put " in the "Text Qualifier" box - the default is <none>

ed

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 2

Yes...I changed the 'Text Qualifier' as 'Double Quote {"}' which I refered from SQL 2000 import method.

Still it was working..

edwardelliott SSCommitted Points: 1863 More actions · Answer 3

If you save your ssis package and a copy of the csv with a few lines in it - can you post it here?

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 4

Hi Ed,

I have not configured SSIS package but was using import wizard in which I was facing the problem.

I pasted the adcollection.csv file and table definition script because in SQL 2000 it automatically create table with all columns of Varchar (8000) and generate out of memory error.

Please try the import in SQL 2005 and let me know.

.CSV file

===============================================================

DN,objectClass,distinguishedName,instanceType,whenCreated,whenChanged,subRefs,uSNCreated,repsTo,repsFrom,uSNChanged,name,objectGUID,replUpToDateVector,creationTime,forceLogoff,lockoutDuration,lockOutObservationWindow,lockoutThreshold,maxPwdAge,minPwdAge,minPwdLength,modifiedCountAtLastProm,nextRid,pwdProperties,pwdHistoryLength,objectSid,serverState,uASCompat,modifiedCount,auditingPolicy,nTMixedDomain,rIDManagerReference,fSMORoleOwner,systemFlags,wellKnownObjects,objectCategory,isCriticalSystemObject,gPLink,gPOptions,masteredBy,ms-DS-MachineAccountQuota,msDS-Behavior-Version,msDS-PerUserTrustQuota,msDS-AllUsersTrustQuota,msDS-PerUserTrustTombstonesQuota,msDs-masteredBy,dc,cn,description,showInAdvancedViewOnly,dSCorePropagationData,ou,msDS-TombstoneQuotaFactor,displayName,flags,versionNumber,gPCFunctionalityVersion,gPCFileSysPath,gPCMachineExtensionNames,gPCUserExtensionNames,ipsecName,ipsecID,ipsecDataType,ipsecData,ipsecISAKMPReference,ipsecNFAReference,ipsecOwnersReference,ipsecNegotiationPolicyReference,ipsecFilterReference,iPSECNegotiationPolicyType,iPSECNegotiationPolicyAction,revision,memberOf,userAccountControl,badPwdCount,codePage,countryCode,badPasswordTime,lastLogoff,lastLogon,pwdLastSet,primaryGroupID,adminCount,accountExpires,logonCount,sAMAccountName,sAMAccountType,member,groupType,localPolicyFlags,operatingSystem,operatingSystemVersion,operatingSystemServicePack,serverReferenceBL,dNSHostName,rIDSetReferences,servicePrincipalName,frsComputerReferenceBL,lastLogonTimestamp,rIDAvailablePool,rIDAllocationPool,rIDPreviousAllocationPool,rIDUsedPool,rIDNextRID,dnsRecord,fRSReplicaSetType,fRSVersionGUID,fRSFileFilter,fRSReplicaSetGUID,fRSPrimaryMember,serverReference,frsComputerReference,fRSMemberReferenceBL,fRSWorkingPath,fRSRootPath,fRSStagingPath,fRSMemberReference,lastSetTime,priorSetTime,location,sn,givenName,userPrincipalName,homeMTA,proxyAddresses,homeMDB,mDBUseDefaults,mailNickname,showInAddressBook,legacyExchangeDN,textEncodedORAddress,mail,msExchHomeServerName,msExchALObjectVersion,msExchMailboxSecurityDescriptor,msExchUserAccountControl,msExchMailboxGuid,msExchPoliciesIncluded,objectVersion,deliveryMechanism,msExchHideFromAddressLists,msExchMasterAccountSid,targetAddress,msExchPFTreeType,mSMQSignCertificates,mSMQDigests,mSMQSites,mSMQServiceType,mSMQOSType,mSMQEncryptKey,mSMQSignKey,mSMQDependentClientServices,mSMQRoutingServices,mSMQDsServices,keywords,serviceClassName,serviceBindingInformation,serviceDNSName,serviceDNSNameType,mS-DS-CreatorSID,title,operatorCount,lastKnownParent,physicalDeliveryOfficeName,telephoneNumber,facsimileTelephoneNumber,department,wWWHomePage,manager,mobile,userParameters,msNPAllowDialin,directReports,logonHours,comment,mSSMSSiteCode,mSSMSSiteBoundaries,mSSMSRoamingBoundaries,mSSMSAssignmentSiteCode,mSSMSDefaultMP,mSSMSMPName,mSSMSDeviceManagementPoint

"DC=lab,DC=du,DC=ae",domainDNS,"DC=lab,DC=du,DC=ae",5,20071229193747.0Z,20081020112721.0Z,"DC=ForestDnsZones,DC=lab,DC=du,DC=ae;DC=DomainDnsZones,DC=lab,DC=du,DC=ae;CN=Configuration,DC=lab,DC=du,DC=ae",4098,X'01000000000000001201000000000000fd963dff02000000fd963dff0200000000000000d80000003a000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000062606adc04be554f93edf098733bbea4000000000000000000000000000000000000000000000000000000000000000000000000000000003600000064633661363036322d626530342d346635352d393365642d6630393837333362626561342e5f6d736463732e6c61622e64752e616500',X'0100000000000000120100000000000032993dff0200000032993dff0200000000000000d80000003a000000700000001111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110000000064c72a0200000000000000000000000064c72a020000000062606adc04be554f93edf098733bbea43daa60d624c28f4cb4cdfeba7dcadb040000000000000000000000000000000000000000000000003600000064633661363036322d626530342d346635352d393365642d6630393837333362626561342e5f6d736463732e6c61622e64752e616500';X'010000000000000012010000d2130000b15627fe02000000d08f3dff02000000ba060000d80000003a00000070000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000006baf03000000000000000000000000006baf03000000000002e96057258d6d48a144f537bb7343a9100676a0f439d24280f5900b37bf283a0000000000000000000000000000000000000000000000003600000035373630653930322d386432352d343836642d613134342d6635333762623733343361392e5f6d736463732e6c61622e64752e616500',34852838,lab,X'2fd6e6f75e175e449cece4bed950b4cc',X'020000000000000004000000000000007ad2ca4ce075044a8176501b567b003d7a340000000000008d2c89fd02000000ded1d77f3a2c17438f9b1a5482c6f902875e010000000000925627fe02000000100676a0f439d24280f5900b37bf283a71af0300000000008a5627fe020000003daa60d624c28f4cb4cdfeba7dcadb047dc72a020000000032993dff02000000',128434437035559632,-9223372036854775808,-18000000000,-18000000000,0,-37108517437440,-864000000000,7,0,1003,1,24,X'0104000000000005150000003070a843191bfd9277644258',1,1,811,X'0001',0,"CN=RID Manager$,CN=System,DC=lab,DC=du,DC=ae","CN=NTDS Settings,CN=LB01ADSS01,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae",-1946157056,"B:32:AA312825768811D1ADED00C04FD8D5CD:CN=Computers,DC=lab,DC=du,DC=ae;B:32:6227F0AF1FC2410D8E3BB10615BB5B0F:CN=NTDS Quotas,DC=lab,DC=du,DC=ae;B:32:F4BE92A4C777485E878E9421D53087DB:CN=Microsoft,CN=Program Data,DC=lab,DC=du,DC=ae;B:32:09460C08AE1E4A4EA0F64AEE7DAA1E5A:CN=Program Data,DC=lab,DC=du,DC=ae;B:32:22B70C67D56E4EFB91E9300FCA3DC1AA:CN=ForeignSecurityPrincipals,DC=lab,DC=du,DC=ae;B:32:18E2EA80684F11D2B9AA00C04F79F805:CN=Deleted Objects,DC=lab,DC=du,DC=ae;B:32:2FBAC1870ADE11D297C400C04FD8D5CD:CN=Infrastructure,DC=lab,DC=du,DC=ae;B:32:AB8153B7768811D1ADED00C04FD8D5CD:CN=LostAndFound,DC=lab,DC=du,DC=ae;B:32:AB1D30F3768811D1ADED00C04FD8D5CD:CN=System,DC=lab,DC=du,DC=ae;B:32:A361B2FFFFD211D1AA4B00C04FD7D83A:OU=Domain Controllers,DC=lab,DC=du,DC=ae;B:32:A9D1CA15768811D1ADED00C04FD8D5CD:CN=Users,DC=lab,DC=du,DC=ae","CN=Domain-DNS,CN=Schema,CN=Configuration,DC=lab,DC=du,DC=ae",TRUE,"[LDAP://CN={516D6B99-35CA-47E9-875F-20FCF5082EC5},CN=Policies,CN=System,DC=lab,DC=du,DC=ae\;0][LDAP://CN={31B2F340-016D-11D2-945F-00C04FB984F9},CN=Policies,CN=System,DC=lab,DC=du,DC=ae\;0]",0,"CN=NTDS Settings,CN=LB01RSADS01,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae;CN=NTDS Settings,CN=LB01RSADS02,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae;CN=NTDS Settings,CN=LB01ADSS02,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae;CN=NTDS Settings,CN=LB01ADSS01,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae",10,2,1,1000,10,"CN=NTDS Settings,CN=LB01RSADS01,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae;CN=NTDS Settings,CN=LB01RSADS02,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae;CN=NTDS Settings,CN=LB01ADSS02,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae;CN=NTDS Settings,CN=LB01ADSS01,CN=Servers,CN=Default-First-Site-Name,CN=Sites,CN=Configuration,DC=lab,DC=du,DC=ae",lab,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

=======================================================================================

Table definition

====================================================================

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[adusers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[adusers]

GO

CREATE TABLE [dbo].[adusers] (

[DN] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[objectClass] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[distinguishedName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[instanceType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[whenCreated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[whenChanged] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[subRefs] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[uSNCreated] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[repsTo] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[repsFrom] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[uSNChanged] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[objectGUID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[replUpToDateVector] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[creationTime] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[forceLogoff] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lockoutDuration] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lockOutObservationWindow] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lockoutThreshold] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[maxPwdAge] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[minPwdAge] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[minPwdLength] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[modifiedCountAtLastProm] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[nextRid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[pwdProperties] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[pwdHistoryLength] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[objectSid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serverState] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[uASCompat] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[modifiedCount] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[auditingPolicy] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[nTMixedDomain] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDManagerReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fSMORoleOwner] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[systemFlags] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[wellKnownObjects] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[objectCategory] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[isCriticalSystemObject] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gPLink] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gPOptions] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[masteredBy] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ms-DS-MachineAccountQuota] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msDS-Behavior-Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msDS-PerUserTrustQuota] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msDS-AllUsersTrustQuota] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msDS-PerUserTrustTombstonesQuota] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msDs-masteredBy] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[cn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[showInAdvancedViewOnly] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dSCorePropagationData] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ou] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msDS-TombstoneQuotaFactor] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[displayName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[flags] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[versionNumber] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gPCFunctionalityVersion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gPCFileSysPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gPCMachineExtensionNames] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[gPCUserExtensionNames] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecDataType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecData] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecISAKMPReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecNFAReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecOwnersReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecNegotiationPolicyReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ipsecFilterReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[iPSECNegotiationPolicyType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[iPSECNegotiationPolicyAction] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[revision] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[memberOf] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[userAccountControl] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[badPwdCount] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[codePage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[countryCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[badPasswordTime] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lastLogoff] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lastLogon] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[pwdLastSet] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[primaryGroupID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[adminCount] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[accountExpires] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[logonCount] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[sAMAccountName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[sAMAccountType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[member] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[groupType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[localPolicyFlags] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[operatingSystem] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[operatingSystemVersion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[operatingSystemServicePack] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serverReferenceBL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dNSHostName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDSetReferences] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[servicePrincipalName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[frsComputerReferenceBL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lastLogonTimestamp] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDAvailablePool] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDAllocationPool] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDPreviousAllocationPool] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDUsedPool] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[rIDNextRID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[dnsRecord] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSReplicaSetType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSVersionGUID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSFileFilter] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSReplicaSetGUID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSPrimaryMember] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serverReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[frsComputerReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSMemberReferenceBL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSWorkingPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSRootPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSStagingPath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[fRSMemberReference] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lastSetTime] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[priorSetTime] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[location] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[sn] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[givenName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[userPrincipalName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[homeMTA] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[proxyAddresses] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[homeMDB] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mDBUseDefaults] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mailNickname] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[showInAddressBook] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[legacyExchangeDN] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[textEncodedORAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mail] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchHomeServerName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchALObjectVersion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchMailboxSecurityDescriptor] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchUserAccountControl] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchMailboxGuid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchPoliciesIncluded] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[objectVersion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[deliveryMechanism] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchHideFromAddressLists] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchMasterAccountSid] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[targetAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msExchPFTreeType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQSignCertificates] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQDigests] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQSites] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQServiceType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQOSType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQEncryptKey] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQSignKey] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQDependentClientServices] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQRoutingServices] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSMQDsServices] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[keywords] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serviceClassName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serviceBindingInformation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serviceDNSName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serviceDNSNameType] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mS-DS-CreatorSID] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[operatorCount] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[lastKnownParent] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[physicalDeliveryOfficeName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[telephoneNumber] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[facsimileTelephoneNumber] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[department] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[wWWHomePage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[manager] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mobile] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[userParameters] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[msNPAllowDialin] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[directReports] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[logonHours] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSSiteCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSSiteBoundaries] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSRoamingBoundaries] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSAssignmentSiteCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSDefaultMP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSMPName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[mSSMSDeviceManagementPoint] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

=====================================================================================

BR,

Parthi

edwardelliott SSCommitted Points: 1863 More actions · Answer 5

OK I have managed to import it and it splits it up into the correct rows, this is what I did using the client components version 9.00.4028.00:

Choose "Flat File Source"

Browse to the csv file

Tick "Column names in the first data row"

Change "Text Qualifier" from to "

Click columns - does the data appear as it should in the preview?

Click advance - because some columns repsTo for example can be longer than 50 chars long highlight them all and set the OutputColumnWidth to 4000 or determine which columns can be longer then 50.

Click Preview - do they show correctly there?

goto next and fill in the sql native client and then set your destination table and import the data. On my machine I had some problems using the native client but as I have 2008 client installed I was able to use that correctly.

ed

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 6

Hi Ed,

Thanks alot for that and the text qualifier " was missed by me. I tried most of the option except this because I didnt understand the usage of the tab.

Please share with me the table definition code so that I can alter further for my requirement.

BR,

Parthi

edwardelliott SSCommitted Points: 1863 More actions · Answer 7

here you go:

CREATE TABLE [dbo].[adusers](

[DN] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[objectClass] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[distinguishedName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[instanceType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[whenCreated] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[whenChanged] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[subRefs] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[uSNCreated] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[repsTo] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[repsFrom] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[uSNChanged] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[name] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[objectGUID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[replUpToDateVector] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[creationTime] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[forceLogoff] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lockoutDuration] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lockOutObservationWindow] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lockoutThreshold] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[maxPwdAge] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[minPwdAge] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[minPwdLength] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[modifiedCountAtLastProm] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[nextRid] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[pwdProperties] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[pwdHistoryLength] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[objectSid] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serverState] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[uASCompat] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[modifiedCount] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[auditingPolicy] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[nTMixedDomain] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDManagerReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fSMORoleOwner] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[systemFlags] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[wellKnownObjects] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[objectCategory] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[isCriticalSystemObject] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[gPLink] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[gPOptions] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[masteredBy] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ms-DS-MachineAccountQuota] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msDS-Behavior-Version] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msDS-PerUserTrustQuota] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msDS-AllUsersTrustQuota] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msDS-PerUserTrustTombstonesQuota] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msDs-masteredBy] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dc] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[cn] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[description] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[showInAdvancedViewOnly] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dSCorePropagationData] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ou] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msDS-TombstoneQuotaFactor] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[displayName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[flags] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[versionNumber] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[gPCFunctionalityVersion] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[gPCFileSysPath] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[gPCMachineExtensionNames] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[gPCUserExtensionNames] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecDataType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecData] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecISAKMPReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecNFAReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecOwnersReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecNegotiationPolicyReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ipsecFilterReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[iPSECNegotiationPolicyType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[iPSECNegotiationPolicyAction] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[revision] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[memberOf] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[userAccountControl] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[badPwdCount] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[codePage] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[countryCode] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[badPasswordTime] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lastLogoff] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lastLogon] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[pwdLastSet] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[primaryGroupID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[adminCount] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[accountExpires] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[logonCount] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[sAMAccountName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[sAMAccountType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[member] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[groupType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[localPolicyFlags] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[operatingSystem] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[operatingSystemVersion] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[operatingSystemServicePack] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serverReferenceBL] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dNSHostName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDSetReferences] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[servicePrincipalName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[frsComputerReferenceBL] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lastLogonTimestamp] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDAvailablePool] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDAllocationPool] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDPreviousAllocationPool] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDUsedPool] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[rIDNextRID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dnsRecord] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSReplicaSetType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSVersionGUID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSFileFilter] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSReplicaSetGUID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSPrimaryMember] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serverReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[frsComputerReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSMemberReferenceBL] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSWorkingPath] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSRootPath] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSStagingPath] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[fRSMemberReference] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lastSetTime] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[priorSetTime] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[location] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[sn] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[givenName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[userPrincipalName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[homeMTA] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[proxyAddresses] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[homeMDB] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mDBUseDefaults] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mailNickname] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[showInAddressBook] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[legacyExchangeDN] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[textEncodedORAddress] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mail] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchHomeServerName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchALObjectVersion] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchMailboxSecurityDescriptor] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchUserAccountControl] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchMailboxGuid] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchPoliciesIncluded] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[objectVersion] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[deliveryMechanism] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchHideFromAddressLists] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchMasterAccountSid] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[targetAddress] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msExchPFTreeType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQSignCertificates] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQDigests] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQSites] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQServiceType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQOSType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQEncryptKey] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQSignKey] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQDependentClientServices] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQRoutingServices] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSMQDsServices] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[keywords] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serviceClassName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serviceBindingInformation] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serviceDNSName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[serviceDNSNameType] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mS-DS-CreatorSID] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[title] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[operatorCount] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[lastKnownParent] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[physicalDeliveryOfficeName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[telephoneNumber] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[facsimileTelephoneNumber] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[department] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[wWWHomePage] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[manager] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mobile] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[userParameters] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[msNPAllowDialin] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[directReports] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[logonHours] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[comment] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSSiteCode] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSSiteBoundaries] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSRoamingBoundaries] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSAssignmentSiteCode] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSDefaultMP] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSMPName] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[mSSMSDeviceManagementPoint] [nvarchar](4000) SQL_Latin1_General_CP1_CI_AS NULL

)

ed

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 8

Hi Ed,

Thanks alot for the table script.

Even though the table was created with all columns of width 4000, I was faced with the below error continuously

Error 0xc020902a: Data Flow Task: The "output column "subRefs" (34)" failed because truncation occurred, and the truncation row disposition on "output column "subRefs" (34)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

(SQL Server Import and Export Wizard)

I tried changing the 'outputColumnwidth' value to higher value the error occured on other columns. As per

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 9

I solved the error by mentioning appropriate values in ''suggest type" as per the article

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125290

and it worked..

BR,

Parthi

Jerry Hung SSChampion Points: 12968 More actions · Answer 10

I find that SSIS 2008 has improved much better

I had issues/errors involving truncation and unicode (despite nothing was in unicode column nor data) from Access 2003 to SQL Server

2008 automatically warns me and allows me to choose appropriate actions, while 2005SP2 still crapped out every time

this is done in SSMS 2008

SQLServerNewbieMCITP: Database Administrator SQL Server 2005