Friday, June 28, 2013

[SQL 2012] Split one Name Column into Two Colmns using SSIS

[SQL 2012] Split one Name Column into Two Colmns using SSIS

Split one Name Column into Two Colmns using SSIS

Posted: 27 Jun 2013 07:16 AM PDT

I am working with SQL Server 2012 and Visual Studio 2010 with Excel 2007. I am building a package that will pull information from a .CSV file and import it into a table in a DB that we are creating for an HRIS project.The .CSV file has the following fields starting in B9 and running through column J (row length will vary)| PersNo | IDNo | Name | PArea | OrgUnit | OrgName | UserID | EntryDate |The issue I have is that I need to take the Name column and split it into a FirstName and LastName columns. I have scoured the internet and found a number of forums that detail methods that use Derived Columns, Conditional Split, Script Component and MultiCast. None of them were helpful or fit my needs.The issue is complicated by the name arrangement. I have three different name types that can be displayed.John SmithJohn A SmithJohn Smith IIIThe last two they will need to be split differently.If there is a middle initial they want it split as such.| FirstName | LastName |------------------------| John | Smith A |i.e. Smith and the Middle Initial will be in the LastName columnIf they have a suffix then they will need to be split as such.| FirstName | LastName |-------------------------| John | Smith III |i.e. Smith and the suffix will be in the LastName columnI need to know if there is a way that I can split this out in one package. Eventually this will be automated to run daily so I will also have to utilize a lookup I assume to filter out any duplicates and enter only new data.I appreciate anything information that anyone can provide.

SSIS 2012 Package run error with Script task.

Posted: 28 Jun 2013 12:14 AM PDT

Hi All,I have been looking for a solution for this to see if someone has encountered it before and I am on the verge of giving it up...We have SSIS packages(specifically Script Task) that have been created in VS2008 and with a .net 3.5 compatibility for the Script task. Can I run this packages using the dtexec in a 2012 server? Currently I am unable to do so on one of my test servers..What i understood from looking up online on Tech net is this : For 2012 :"The scripting engine has been upgraded to VSTA 3.0, which gives us a Visual Studio 2010 shell, and support for .NET 4. and the Script task in SSIS 2008 is using .Net framework 3.5, so that there have some incompatibilities "However that being said we have QA server where this ran just fine. It gave warnings that the package needs to be migrated; but it worked just fine.I checked the config of both the server where this packages are run. The CMD file invokes the right location of dtexec with the right provider of SQLNCLI11 C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exeBoth have 3.5 and 4.5 installed.. Am i missing something here? If anyone can provide any insights that would be great..Again..converting the package to the new 2012 is currently not an option and is a last resort..But since it runs on a QA server, I am just baffled :crazy:

DBmail stopped working

Posted: 27 Jun 2013 11:42 PM PDT

Hi,I have a newMicrosoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)While trying to alter sp_db_mail, my dbmail stopped working. I am getting below error in logs:Message1) Exception Information===================Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseExceptionMessage: The Transaction not longer valid.Data: System.Collections.ListDictionaryInternalTargetSite: Void ValidateConnectionAndTransaction()HelpLink: NULLSource: DatabaseMailEngineStackTrace Information=================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction() at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)Steps I have tried after this issue:1) Recreated send_db_mail procedure from instance running on 20122) Stopped and Started DB mail using sysmail_stop_sp and sysmail_start_sp3) Checked that service broker is running4) SMTP is working fine as DBMAIL using this SMTP on other server are running fine5) Deleted all unsent emails6) Rebooted the server/services/agent7) Anti-virus also seems to be okay as only this server is throwing errors8) Replaced databasemail.exe and other *.dll's from other 2012 serverAny help would be appreciated!Thanks

SSIS - MOVE File with File Sysyem Task

Posted: 27 Jun 2013 10:12 PM PDT

Hi there, Simply want to move a .BAK file from one location to the other..BAK starts in the following location - \\mhsvi-mgmt03\e$\oneserve_DW\ExportFromOneserveThe destination that I want it to end up is - E:\Oneserve Datacut (This is the E:\ where the SSIS is running from)A variable called bakfile has been created = MHS_DataCut_PROD.bakTwo paramters exsist = destinationpath = E:\Oneserve Datacut\ = sourcepath = \\mhsvi-mgmt03\e$\oneserve_DW\ExportFromOneserve\Two File Sources exsist which uses the following expressions - Backup File Source = @[$Project::sourcepath] + @[User::bakfile]Backup File Destination = @[$Project::destinationpath] + @[User::bakfile]When I add a File System task to my SSIS and simply copy the file it completes fine.But when I change it to move....it errors??? It has the exact same connection source and destination I don't understand why the error simply tells me in the Execution Results - [File System Task] Error: An error occurred with the following error message: "Could not find a part of the path.".Can't for the life of me understand why

SSIS Package - Oracle client and networking components were not found.

Posted: 27 Jun 2013 09:38 AM PDT

We are running SQL Server 2012 EE 64-Bit Eval Copy on Windows Server 2008 R2 EE 64-Bit SP1 on our DB Server. We have installed Oracle 11G Client Install. We did a Custom Install and selected the following:SQL*PlusOracle NetOracle ODBC DriverOracle Services For Microsoft Transaction ServerOracle Objects for OLEOracle Provider for OLE DBOracle Data Provider for .NETConfigured sqlnet.ora and tnsnames.ora files. Tested tnsping to our Oracle Server/Database and it worked.Tested sqlplus to our Oracle Server/Database and it worked.Tested ODBC (System DSN and 'Oracle in OraClient11g_home1' driver) to our Oracle Server/Database and it worked.Set up Linked SQL Server to Oracle using the following and it worked:--With Microsoft OLE DB Provider for Oracle, use the Oracle server alias --(that is configured in the TNSNames.Ora file) for the @datasrc parameter.-------------------------------------------------------------------------- Test_DBTest Link-------------------------------------------------------------------------- Add a linked server for Test DBTest.USE [master]GOEXEC sp_addlinkedserver @server = 'Test_DBTest_shared' ,@srvproduct = 'DBTest' ,@provider = 'OraOLEDB.Oracle' ,@datasrc = 'DBTest'GO-- Add a login for the the remote linked server.USE [master]GOEXEC sp_addlinkedsrvlogin @rmtsrvname = 'Test_DBTest_shared' ,@useself = 'False' ,@locallogin = NULL ,@rmtuser = 'shared' ,@rmtpassword = 'Test1232' Then to create the SSIS Package, I go into Microsoft Visual Studio 2012 (it opens and displays Microsoft Visual Studio 2010 Shell). When I try to create a new OLE DB Connection to Oracle via Connection Manager, I select the "Native OLE DB\Microsoft OLE DB Provider to Oracle" I get the following error:"Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed."The Connection Manager does not display any other Oracle Providers. I saw the following during my research:"ProblemsBecause BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client."Is BIDS and Visual Studio the same application or uses the same code? Since, I installed SQL Server 2012 EE 64-Bit Eval Copy and the OS is 64-Bit, does this mean a 64-Bit version of Visual Studio was installed? What may I try to get this resolved?Thanks in advance, Kevin

SQL SERVER 2012 INSTALLATION Error 1311.Source file not found: C:\Users\pc\Desktop\Sqlserver2012\redist\VisualStudioShell\

Posted: 27 Jun 2013 08:07 AM PDT

TITLE: Microsoft SQL Server 2012 Setup------------------------------The following error has occurred:Error 1311.Source file not found: C:\Users\pc\Desktop\Sqlserver2012\redist\VisualStudioShell\VC10SP1\x64\vc_red.cab. Verify that the file exists and that you can access it.Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2100.60&EvtType=0xDF039760%25401201%25401------------------------------BUTTONS:&RetryCancel------------------------------this is sql server 2012 Eval Edition and I'm getting this error any time I want to install. I appreciate if you know and want to help me.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog