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

How to resolve culture problem of decimal and thousand separator  of number of excel using c#
1
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

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
Hi,

I am in the process of migrating a SQL server 2005 to a SQL server 2012.     On the 2005 server,  SSIS packages are used to upload CSV data into various databases.  These SSIS packages are created in Visual studio 2005.

My questions are around the versions of software I require on the new server:

1.  Will the new SQL 2012 server require an install of visual studio? if so, which version?  It would only be used for SSIS development - would an express version be suitable?
2. Are there any migrating tools to help?
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
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
Angular Fundamentals
LVL 12
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

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
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
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Hi,

I've got an SSIS package that looks in a folder for zip files and if it finds them it unzips all of them using C# using a reference to system.io.compression.

This is the example I've used:  example

Here is my code:
using Microsoft.SqlServer.Dts.Runtime;
using System.Runtime.InteropServices;
#region Namespaces
using System;
using System.Data;
using System.Windows.Forms;
#endregion
using System.IO.Compression;
using System.IO;
[assembly: ComVisible(false)]
namespace ST_51121ce851f54f58bb7a111e9711bfb7
{
  
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
     
		public void Main()
		{
          
            string zipfullpath = Dts.Variables["User::ZipFullPath"].Value.ToString();
            string inputfolder = Dts.Variables["$Package::InputFolder"].Value.ToString();

            using (ZipArchive arch = ZipFile.OpenRead(zipfullpath))
            { foreach(ZipArchiveEntry entry in arch.Entries)
                {

                    try
                    {
                        entry.ExtractToFile(Path.Combine(inputfolder, entry.FullName));
                    }
                    catch (Exception ex)
                    {
                        //local variable to update error description
                        

Open in new window

0
Hello Experts,
I am working on a SSIS package, which was converted from DTS package.  In Script Task, I have some codes, where I am trying to check the existence of the network file.  For some reason, I am getting the runtime errors below.  Please let me know what is the error and how to fix it.

Thank you very much in advance.


Dim obj As Object
If (Not obj.FileExists(strNetworkFile)) Then
     Dts.Variables("TaskResult_Failure").Value = True
     Exit Sub
End If

Errors
---------------
Implicit conversation from 'Object' to 'Boolean'.
Late bound resolution; runtime errors could occur.
Operands of type Object used for operator 'Not'; runtime errors could occur.
0
Hello Experts,
I am trying to open a DTS package in Visual Studio 2015.  The goal is to convert it to SSIS package.   I was able to open the DTS package with error in Integration Services Script Task, where I have VB codes.  When tried to edit it, I received "This task does not have a custom editor. Use the properties window to edit properties of this task"
 message.  Any idea how to edit it?  Please try to help.  Thank you in advance.

Thank you!
0
Hello Experts,
I am trying to rewrite a DTS package in SSIS and trying to make use of the existing codes defined under ActiveX Script Task.  When copied and pasted in Integration Services Script Task in SSIS package, I received some errors as below.  I am trying to find the place where they are declared as I don't see these variables defined in the program.  Please see if you can help.  Thank you in advance.

Error      1      Expression is a value and therefore cannot be the target of an assignment.      
Error      2      Name 'StepScript_TaskDontExecute' is not declared.      
Error      3      'SourceFileExists' is not a member of 'String'
0
How do you change SSIS 2014 package variables. I can see the variables but they are grayed out.  
This SSIS Project was recently migrated from 2008 to 2014 by our migration team.
For instance, I would like to change variable DispatchExcellenceServer from DISPEXCDEVMIG to DISPEXCDEV.
0
The application executes the SQL stored procedure to run the package and package executes as proxy account. We can schedule same package to run at same time with different variables or source files as input and when scheduled to run at same time, one package completes the execution and other package fails with below error. If there is some time gap then both packages succeeds.Can someone suggest how to fix the issue?

Started:  10:13:06 AM
Error: 2018-05-21 10:13:06.78
   Code: 0x00000001
   Source: Script Task
   Description: Exception has been thrown by the target of an invocation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  10:13:06 AM
Finished: 10:13:06 AM
Elapsed:  0.795 seconds
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.