[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have an SSIS package that is giving me an error in our test environment. It runs fine in DEV, but errors in test. It is a data flow task with an OLE DB source that executes a SQL Servr stored procedure. The code is:
EXEC [uspME-GF-30250-HMS_HD] ?,?,?,?
WITH RESULT SETS(
(	
	ORDERS VARCHAR(1),
	RECORDS VARCHAR(1000)
 )
)

Open in new window

The error is:
Error: 2018-11-14 09:24:03.48
   Code: 0xC0202009
   Source: DFT_LOAD_MEMBER_EXTRACT_FOR_HMS OLE_SRC_MEMBER_EXTRACT [9]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Invalid column name 'CXCX_RECORD_NO'.".
End Error

I thought of removing the WITH RESULT SETS but I can't even save the changes. SSIS gives me the following error:
The metadata could not be determined because the statement 'INSERT INTO #StaticData...' uses a temp table.
The stored procedure is 'relatively' complex, creating and loading several temp tables, some intermediate staging tables, them finally building the data extract result. I've never run across this issue before and am looking for a solution. Checking Google for…
0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I have SSIS package created which transfers data from local mysql server to azure database.  the package runs fine inside visual studio and data gets transferred.  i created the sql job in local sql 2012 server by importing this package .  when i run the job  it throws the below error and job fails

sqlsvrjob_medicsteam_azure Connection manager "khsql.database.windows.net.Kh-Website.khsql"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'khsql'.".
0
Hi All,

I'm new to SSIS and I want to do incremental data load for 80 tables from one database to a other database and  I want to  to do it through CDC of SSIS.
Can anyone help me out abut this?

Or please let me know if any  the way to get only the delta from all 80 tables in one database and load into target tables in different database every day without using CDC of SSIS.

Regards,
NPK
0
I tried to install Visual Studio 2017 Professional but it failed due to lack of disk space.

So when I opened my Visual Studio 2008 SSIS project, I get this error:

Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (System.Windows.Forms)
0
In Visual Studio, Integration Services, I have a SQL source and an Excel destination.  Is it possible to create an initial step that deletes the contents of the Excel file, except for the column headings.
0
Hi,

I have a DTS job that uploads some CSV data into a SQL table.

I wanted to schedual the DTS using an SQL job.

I have setup the job in SQL but when it is executed through the SQL job it fails (see error message below).

If I run the DTS directly it works ok.

I think it is something to do with the user permissions but I don't know why it fails as SQL user I have setup (web_admin) has SA rights.

Any ideas?

DTS fail
0
Hi , I would like to load data from 3 different SQL Command (3 different queries) in 3 different excel files in one folder with each excel file with Date time feature in One package .
I tried this solution however getting issue as It wont open mapping window for 2nd Excel destination. However 1 excel file is loaded successfully., Your help is appreciated

Error is:

Excel destination: Opening a rowset failed. Checked that objects exists in Database.
0
How to resolve culture problem of decimal and thousand separator  of number of excel using c#
1
Hi,

I'm using SSIS 2008.

I have 2 ssis packages.
QUESTION:
1. can packageA call packageB ?   How (please show example code)?
2. Will packageA task that executes packageB wait until packageB is completed before packageA goes on to process a subsequent task?

Thanks.
0
Help with C# code to delete first row of all Worksheets in an Excel Workbook using SSIS script task.
There are about 17 worksheets, the script needs to loop through and delete the first row.

 Loop-through-Excel-sheets-and-delete.sql
test.xlsx
0
OWASP: Threats Fundamentals
LVL 12
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Hello,

I am getting below error while connecting Oracle DB from Visual Studio 2015 - SSIS. Can you help me to fix below error?
MS SQL Version: MS SQL 2016 Ent Edition
Oracle client version: Oracle 11g 64 bit (Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64) )
ODAC version: ODAC 11.2 Release 5 and Oracle Developer Tools for Visual Studio (11.2.0.3.20) (32 bit)

I have also tried with 64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64, but still same error. There are No multiple 32-bit client libraries (SQAORA32.dll) on the same machine.

I am able ro connect oracle DB using SQL plus utility but geeting error in SSIS.
0
Hi,

I'm using SQL SERVER 2008R2 and SSIS 2008.  How do I export a SQL SERVER view to Excel .xls format?  I've done it a long time ago.

In the DTS task, I've set the fields to DATA TYPE=UNICODE STRING [DT_WSTR].

Problem I'm running into is the next task  EXCEL DESTINATION: when I click MAPPINGS, the right side of the screen just says F1  ssis_export_to_excel.JPG
0
I have created SSIS package in VS 2012 and SQL server 2012, it is working properly V.S. on the local machine
but  I am facing One issue when I am executing my package using a batch file on Server  
I got one error as
Error: 2018-08-30 07:37:43.57
Code: 0x00000001
Source: Script Task
Description: Exception has been thrown by the target of an invocation.
End Error

Can you please  provide a solution how to fix this issue
0
I am currently using a fore each file enumerator within SSIS to pick up the names of files and pass then to a script for processing. There are 2 parts to the process. I use a variable to hold the file name and another variable to hold the file path. For debugging I have set message boxes to display firstly the file name and another to display the full path to the file. The process is not picking up the file name so the first message box is empty. The second only displays the path to the folder where the file should be.

Any one any ideas on this?

Thanks
0
How to access secrets stored in Azure Key Vault in SSIS? Can we access it using script task or is there another way to achieve this? I tried a C# code in SSIS Script Task functionality to access Key Vault, for that, I installed some NuGet packages in my solution. But when I save and exit script task and open it again, all the NuGet packages I installed have vanished from the solution, so one more question arises here, does SSIS Script Task supports NuGet functionality?
0
We have a database on-Prem which we want it to sync with Azure DB. So all the changes that we do on-Prem should appear on Azure DB next day. To accomplish that I tried data Sync option and that option only syncs Tables and syncs data creating lot of data sync objects on our on-Prem DB. I tried to take a backup copy of DB and put it on Blob Store and from there tried to restore to Azure DB but we are using Azure SQL Server DB which does not support restored DB option from backup file unless its a SQL DB on a managed instance.

So what other options do I have? Copy data in Data factory only copies data are there any other Azure tools that I can use to sync both the DB's
0
I  am looking for the best way to use SSIS to export contact data from SQL Server 2016 to an Outlook 2016 contact folder.  The contact data come from a view with over 5000 records.
0
i'm looking for a way to web scrape a simple web page by taking a csv list as my parameter and retrieving the html table on the next page through SSIS
The webpage is http://p2web.energy.dla.mil/pls/p2wp/dfsc_pkg.df_contract

so in the contract textbox i would put contract number item in my csv and retrieve the following html element

table
not wanting to use Zappysys. wondering if there are other tools out there that are free.
0
Hi Expert,
I need to create Degenerate Dimensions Table? Can any one show me how to do that?
0
OWASP Proactive Controls
LVL 12
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Creating an SSIS app with VS 2015.  Finally have the pieces connected.  When I run it I receive the below error.
===================================

Failed to start project (Microsoft Visual Studio)

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

Error starting debugging. (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
Program Location:

   at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.LaunchVsDebugger(IVsDebugger iVsDebugger, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

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

Unable to 

Open in new window

0
I am looking for an option of Active Directory Authentication in SSIS Connection Manager. Is there any such feature available? I can only see Windows and SQL Server Authentication as of now.
0
I have a series of SSIS projects that run automatically each morning. Part of what they do is attempt to standardize addresses and retrieve latitude and longitude values from a master table. This master tables does get updated every few months. My question is this. Is it possible within SSIS to detect if a file exists in a folder and if it does proceed with the import and when the import is done to delete this file? I don't want the project to run on the same data morning after morning but only when an updated file is dropped into this folder. This input file is an Excel spreadsheet and the destination is a SQL table.
0
Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "OLEDB_SRC" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ProjectPPE_LoadWorkdayData:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80004005  Description: "ORA-12154: TNS:could not resolve the connect identifier specified
0
Hello Experts ,

Where can I find template for ETL mapping document ?

Looking for best practices

Are you using Excel or any tools ?

Looking fwd to hear back
0
Hi - I was wondering if someone can advise please. Our organisation uses a ERP systems which has 4000 Relations tables. There is a discussions on creating a master database - where any changes to these tables are populated into Master database. I am worried about this approach - for example, there is no business logic and any changes gets sent to this MD, which is used for integration with other systems? I would have thought you use master database to bring in data from various systems rather than just duplicating one system into another.

The argument for creating a master database is so there is no need for peer to peer integration - I agree peer to peer is not good but thats what what BizTalk is there for. They want to use BizTalk to pull data from the ERP into Master Database and then use SSIS to push data out to other systems. I can't see why they can't they just use BizTalk to transform the data and push the data out. Their other argument if the ERP system ever goes cloud it is not possible to do a full extract (i.e, not delta extract) using SSIS - but the issue nobody even know if we will ever even go hosted solution for the ERP we have. Additionally, even in such a case if there is a good business case I am sure the cloud prvoide will let SSIS access directly to database on the hosted cloud. Do you agree? Can you give your inputs on the flaws with this approach or is it just me worrying?

Thank you.
0

SSIS

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.