Tag: ssis

Split a single column of data with comma delimiters into multiple columns in SSIS

I have a table in SQL Server with 3 columns, one of which is a data column containing rows of concatenated columns delimited by commas. The first row is also the header row of the new table I want to create. so basically I want to turn this. Data | ID | Source ==================== a,b,c,d,e […]

Import CSV File Error : Column Value containing column delimiter

I am trying to Import a Csv File into SQL SERVER using SSIS Here’s an example how data looks like Student_Name,Student_DOB,Student_ID,Student_Notes,Student_Gender,Student_Mother_Name Joseph Jade,2005-01-01,1,Good listener,Male,Amy Amy Jade,2006-01-01,1,Good in science,Female,Amy …. Csv Columns are not containing text qualifiers (quotations) I Created a simple package using SSIS to import it into SQL but sometime the data in SQL […]

insert XML file in SQL via SSIS

I have this procedure ALTER proc [dbo].[adenti] ( @entra nvarchar(max) ) as DECLARE @sql varchar(4000); SET @sql = ‘INSERT INTO provaxml (arquivo) SELECT CAST(BulkColumn AS XML) FROM OPENROWSET(BULK ”’ + @entra + ”’, SINGLE_BLOB) as arquivo’; EXEC( @sql ); The code above works. Is it possible to use Integration Services to insert all the XML […]

How to modify the variable in SSIS?

I have a simple String variable with the following value: “C:\Test.txt”. Now I would like to edit the variable to point to a different file. I cannot find a way to do that. I can change the Name, Data Type, but not the value itself! Do I need to delete the variable and create the […]

Why NULL values are mapped as 0 in Fact tables?

What is the reason that in measure fields in fact tables (dimensionally modeled data warehouses) NULL values are usually mapped as 0?

SSIS 2012: Trim trailing commas from a CSV output

I have a file, I cannot control the output, that has a trailing comma on each data row and not the header row. “Pid”,”Dtdate”,”Tencode”,”Stype”,”Amount”,”Currentbal” “13773793”,”05/26/2014″,”Abc123″,”PAYMENT”,”50.00″,”1807.95″, “13773794”,”05/26/2014″,”Abc123″,”ADJUSTMENT”,”10.00″,”18.95″, “”, I am trying to figure out how to trim each row of the trailing comma so that it will read in SSIS correctly. “Pid”,”Dtdate”,”Tencode”,”Stype”,”Amount”,”Currentbal” “13773793”,”05/26/2014″,”Abc123″,”PAYMENT”,”50.00″,”1807.95″ “13773794”,”05/26/2014″,”Abc123″,”ADJUSTMENT”,”10.00″,”18.95″ “” I […]

Dump SSIS USER variable name and value in SQL Server table

Purpose: Fetch all the user variables that are in the SSIS package and write the variable name and their values in a SQL Server 2008 table. What have I tried: I got a small “Script Task” working to Display the variable name and their value. The script is as follows. using System; using System.Data; using […]

SSIS mysterious error

I got only these 2 errors when converting flat files to database tables. The dataflow seems to work for other flat files with the same layout so I don’t understand where these errors could come from. [Flat File Source [204]] Error: An error occurred while skipping data rows. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The […]

CAST vs ssis data flow implicit conversion difference

I have a SSIS package which transfers some data from Oracle to SQL Server. In Oracle dates are stored as float, e.g. 42824 == ‘2017-04-01’ – application which uses the database is written in Delphi. While select CAST(42824 as datetime) in Management Studio results in ‘2017-04-01 00:00:00.000’, the same value (42824) inserted by package into […]

SSIS Connection Manager Not Storing SQL Password

I used to have a dts that had sql server authentication connection. Basically the userid password is stored in the package itself. Now when I go to SSIS the password is not getting stored to the package. I saw this when I google the problem. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c720e694-2f58-483a-9cd7-3feb7de2db7b but no one seem to have given a good […]

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