Tag: ssis

SSIS OLEDB destination with SQL command (Insert if not exists)

Ok so according to Microsoft docs the OLE DB Command Transformation in SSIS does this The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table. So I want to write some […]

How to compare two tables in SSIS? (SQL Server)

I am creating an SSIS package that will compare two tables and then insert data in another table. Which tool shall I use for that? I tried to use “Conditional Split” but it looks like it only takes one table as input and not two. These are my tables: TABLE1 ID Status TABLE2 ID Status […]

Converting DT_NTEXT to DT_Text

So I’m converting a date to MMDDYYYY using the following code: replace(convert(varchar(10), Absent_Date, 101), ‘/’, ”) as Absent_Date When I run it in Navicat it runs fine, but when I use SSIS to export from a stored procedure to a flat file it get the following error message I have read about 20 differnt posts […]

Working with Decimal fields in SSIS

I’m using SQL Server 2008 w/SP2. I’ve got an incoming decimal(9,2) field incoming through my OLE DB transformation to my recordset destination transformation. And I see that when I click on the Input and Output properties tab of the destination Transformation, it’s showing this particular field as type DT_NUMERIC for some reason. It’s like it’s […]

SSIS: Use System::TaskName inside the dataflow

For more detailed logging, I want to retrieve the [System::TaskName] Right now, when starting from the task that fails we go to ‘script task’, there I fetch [System::TaskName] and write that in the log. Logically this writes the current TaskName = ‘Script task’ instead of the failed task Problem is the System::TaskName is only know […]

Package migration from version 8 to version 6 failed with error 0xC001700A

SSIS package built in VS 2013 (v 12.0.31101) DTSExec run on SQL Server 2014 Description: Package migration from version 8 to version 6 failed with error 0xC001700A “The version number in the package is not valid. The version number cannot be greater than current version number.”. I see lots of google results for the above […]

SQL Server – CAST AND DIVIDE

DECLARE @table table(XYZ VARCHAR(8) , id int) INSERT INTO @table SELECT ‘4000’, 1 UNION ALL SELECT ‘3.123’, 2 UNION ALL SELECT ‘7.0’, 3 UNION ALL SELECT ‘80000’, 4 UNION ALL SELECT NULL, 5 Query: SELECT CASE WHEN PATINDEX(‘^[0-9]{1,5}[\.][0-9]{1,3}$’, XYZ) = 0 THEN XYZ WHEN PATINDEX(‘^[0-9]{1,8}$’,XYZ) = 0 THEN CAST(XYZ AS decimal(18,3))/1000 ELSE NULL END FROM […]

How to automate the execution of a stored procedure with an SSIS package?

I have a stored procedure that gets executed through SQL SSIS using a Execute SQL Task. The task has the following: USE [OPPY_DWUSD] GO DECLARE @return_value int EXEC @return_value = [dbo].[generate_merge_scdbk] @Schema = N’dim’, @Dimension = N’VARIETY’, @ETLSchema = N’stg’, @ETLTable = N’vw_VARIETY’, @Execute = 1 SELECT ‘Return Value’ = @return_value GO Right now the […]

SQL SSIS Derived Column formula/syntax issues

I have a formula that I need to be able to put into my SSIS Derived Column… Below it is.. if MONTH(Sls.SlsDt) + DAY(Sls.SlsDt) > MONTH(TODAY) + DAY(TODAY) then dYdtYr = 0. else dYdtYr = YEAR(Sls.SlsDt). How would I put the above business logic into a SQL SSIS ETL Derived Column Transformation? Is Derived Column […]

SSIS – Derived Column Calculation Error

having a few issues with a calculation in a derived column in SSIS. I am trying to perform the following calculation – 4206 + ((4206 * 4206) * 0.000150000000000) + ((4206 * 4206 * 4206) * 0.000000010000000) I am getting an error of – **The magnitude of the result of a binary operation overflows the […]

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