Tag: ssis

Import data into sql server from files with different format

I have a program, which watches a folder on the server. when new files (flat file) come in, the program (C#) read data, bulk insert into the table. it works fine. Now, we extend the system. It means the data files could be in different formats (flat file, csv, txt, excel..), or with different columns […]

Removing specific value from SQL database

Hi i currently have this column in a SQL table, i would like to remove all value of the last bracket Current Column Column xxx Banana Apple(3yy)(Rev 1) Pear(5zx)(Rev 3) orange(9iz)(Minor 5) Cow(7iz)(Roaring 5) Desired Column Column xxx Banana Apple(3yy) Pear(5zx) orange(9iz) Cow(7iz) I am willing to write a package in SSIS or do a […]

Is it possible to overwrite with a SSIS Insert or similar?

I have a .csv file that gets pivoted into 6 million rows during a SSIS package. I have a table in SQLServer 2005 of 25 million + rows. The .csv file has data that duplicates data in the table, is it possible for rows to get updated if it already exists or what would be […]

Check if column value is Numeric. SSIS

I have a column with datatype of varchar. I would like to replace all the values that are not numeric with NULL. So for example my column can contain a value of MIGB_MGW but also 1352. The current expression I am using with Derived Column Transformation Editor is: (DT_I4)kbup == (DT_I4)kbup ? 1 : 0 […]

Tilde (~) Delimited File Read in SSIS

I’m trying to load a Tilde (~) delimited .DAT to SQL Server DB using SSIS. When I use a flat file source to read the file, I don’t see the option of a ~ delimiter. I’m pasting a row from my file below: 7318~97836: LRX PAIN MONTHLY DX~001~ALL OTHER NSAIDs~1043676~001~1043676~001~OSR~401~01~ORALS,SOL,TAB/CAP RE~156720~50MG~ANSAID~100 0170-07 In here, I […]

Conditional Upload with Case

After doing a data migration from an old system, I came across a situation where there are records without dates. The example I give above is the situation I have. I have nowhere to get the data from those NULL. So what is logically set to: Get the Minor date within that range (00013 or […]

How to write to a Variable in script component

I want to write a value to a variable in package using script component using C#. presently i am using these code: public class ScriptMain : UserComponent { string s, r, m; public override void PreExecute() { base.PreExecute(); } public override void PostExecute() { base.PostExecute(); Variables.SNumber = s; Variables.RNumber = r; Variables.MNumber = m; } […]

Job failed with followign error message

SSIS package execution failed with following Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Protocol error in TDS stream”. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: […]

A 99.99 numeric from flat file doesn't want to go in a NUMERIC(4,2)'SQL Server

I have a csv file : 1|1.25 2|23.56 3|58.99 I want to put this value in a SQL Server table with SSIS. I have created my table : CREATE TABLE myTable( ID int, Value numeric(4,2)); My problem is that I have to create a Derived Column Transformation to specify my cast : (DT_NUMERIC,4,2)(REPLACE(Value,”.”,”,”)) Otherwise, SSIS […]

SQL Server 2012 connectivity to Oracle

I am trying to copy data from Oracle to SQL Server 2012, and I get the following message when selecting Data Source as Microsoft OLE DB Provider for Oracle: Test connection failed because of an error in initializing provider. Oracle client and network components were not found. These components are supplied by Oracle Corporation and […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.