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

Hi Expert,
SSIS using Lookup Table , i need matched with Target table
(See attached screenshot)
01.Invoice Number
02.Invoice Sequence
03. Invoice Line
04. Order Number
05. Order Line
06. Order Realse Number
07. SiteID

Then if not matched records fields ,then need to add to Target table (INSERT all the above 7Lookup unmatched feilds Records)

Can some let me know , looking at screenshot it's correct way to matched.
I have used Cached -Partial
0
Cloud Class® Course: Certified Penetration Testing
LVL 12
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Hello community,

I have Googled this topic to death and I cannot find the solution to my problem.  Here is my setup:

Database Server 1:
SQL Server 2014 Developer
WIndows Server 2012 R2 Std

Backup Server 1:
SQL Server 2014 Developer
Windows Server 2012 R2 Std

Backup Server 2:
SQL Server 2014 Developer
Windows Server 2016 Std Eval

On Database Server 1, I have 3 databases: DB1, DB2, DB3.

I have created the following script to back each one up (one-at-a-time) to one of the backup servers:

Mapping Drives:
exec xp_cmdshell 'net use P: /delete'
GO
exec xp_cmdshell 'net use Q: /delete'
GO
exec xp_cmdshell 'net use P: \\192.168.0.200\Backup_F'
GO
exec xp_cmdshell 'net use Q: \\192.168.0.200\Backup_G'
GO
exec xp_cmdshell 'dir P:'
GO
exec xp_cmdshell 'dir Q:'
GO

Open in new window


Actual Backup:
DECLARE 
@today nvarchar(20),
@DB_to_backup nvarchar(128) = 'DB1',
@PATH_FILE1 nvarchar(128) = 'P:\SS_BKP1',
@PATH_FILE2 nvarchar(128) = 'Q:\SS_BKP2',
@backup_type nvarchar(128) = 'diff' /* Specify 'full' or 'diff' */

SELECT @today = SUBSTRING(REPLACE(REPLACE(REPLACE(CONVERT (datetime2,GETDATE(),120),'-',''),':',''),' ','-'),0,16)

DECLARE 
@DISK1 nvarchar(256) = @PATH_FILE1 + '\' + @DB_to_backup + '\' + @DB_to_backup + '_' + @today + '_' + @backup_type + '_MTS4Mb_BufferCount22_file1_of_2.bak',
@DISK2 nvarchar(256) = @PATH_FILE2 + '\' + @DB_to_backup + '\' + @DB_to_backup + '_' + @today + '_' + @backup_type + '_MTS4Mb_BufferCount22_file2_of_2.bak'

PRINT @DISK1

Open in new window

0
Hello Experts,
I am trying to run a SSIS package using Visual Studio 2015.  When execute it with Start Debugging mode, the package runs just fine.  But when run in Start Without Debugging it fails with the below error message.  Any idea what is going on?  Please try to help.  Thank you in advance.

Error Message:  The task has failed to load. The contact information for this task is ""

Thank you!
0
Running Data Flow Task in package through SSIS debug mode (visual studio ssdt 2017) retrieves more rows than calling the package from sql server 2016 Agent job.
Not a rights issue as both run the exact same query and yet return different results.
The data of the query retrieved is in the same locale SQL Server database.
Anybody have a clue how this is possible; I ran it multiple times with exact same different results.
If I run the query in SQL Server Management Studio query window, it gets same results as query when run through debug within VS.
Somehow the SQL Server Agent Job calling the package runs the same query but returns less rows.
0
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
Experts,

I'll be loading hundreds of XML files into the SQL Server and I am wondering what is the best solution out there. I have attached a sample XML file. Is SSIS a better solution to automate this process or are there any third party tools we can use to complete this project?

Regards.
HE_24201_OK.xml
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
Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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
I have a 2012 SSIS package that will take the results from a query and using a flat file connection save the data to a csv file to a local drive on the server. This works but I would like to upload this csv file to a web page so a user can get access to it easily.
0
Using SSIS 2008 R2
Output to Excel 2010

I am using SSIS to export a SQL table to an Excel template. This all works fine (aside from a Date issue which is the subject of another question), but for some reason the exported cells are all formatted with a blue colour.
I've checked that there is no conditional formatting in place on the Excel worksheet. There are no macros either.
I can't find any settings anywhere within SSIS that would cause it to do this; it's not a huge issue as it is cosmetic (the output data is correct), but I would prefer it to not change the cell colour! However, if I have the workbook open when SSIS is exporting the data it does not change the colour of the cells; of course this takes far too long to process.

Cell formatting
0
Hi Experts,

I have to load two different flat files to a table via SSIS. Flat file folder i need to load from has thousands of files such as
Lets say
Customer_abc_def_zxc1-12-2018.txt
Non_Customer_abc_def_zxc10_12-2018.txt

Requirement
-load only above two files that are loaded to the folder every morning(grab latest files both, and ignore old files)again- load only new files from the folder everyday
-  i tried to use c#  code in script task and it pulled different file we are not interested in. I am not good at c# , used it from web to test and it worked in local folder (has few files to test)but grabbed wrong one testing in network folder.(it did pick latest but not the above named files)

-i also want to be able to extract date part from filename above and load them to target table. Source file has no date, we need to use derived column, i did  use substring but it grabbed some string ( date is not named like 01,02 for month else it would hav e been easy)

Source: http://www.techbrothersit.com/2013/12/ssis-how-to-get-most-recent-file-from.html?m=1

There are also other files we are not interested in as
Customer_abc_def_zxc.txt
Non_Customer_abc_def_zxc.tx
Lawndry.txt
Nonpaying_customer.txt and so on 1000s of other txt files

We dont want above latest file

Looking for simple solution in SSIS.

Thanks in advance
0
Hi guys,

In SSIS I have am saving a value of "TRUE" OR "FALSE" in a variable. The data is coming as VARCHAR (I am not allowed to convert varchar in SP)

How to convert this varchar to bool in SSIS and assign it to a variable? Am i doing correct thing in variable window?

@[User::InvokeNoDataReturnError] = (DT_BOOL)"TRUE"?1:0

Open in new window


When i try the above code i get the following error message

Error: Cannot evaluate expression "@[User::InvokeNoDataReturnError] = (DT_BOOL)"TRUE"?1:0" because the variable "User::InvokeNoDataReturnError" does not exist or cannot be accessed for writing. The expression result cannot be assigned to the variable because the variable was not found, or could not be locked for write access.
0
I am trying to install SSIS (Integration Services) for a SQL Server 2016 install. The install works fine, and I can see the services running, however, the app that is using SSIS internally fails and also I cannot connect to an SSIS server. When I try, I do not see a server available. Shouldn't I?

I'm very confused a this point. The error message the 3rd party app is throwing is:

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

When I look at the running services I see:
ssis_services.png
ssis_noserver.png
ssis_class_error.png
0
Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
or one of its dependencies. The system cannot find the file specified

Running a load in an app that uses SSIS Integration Services... getting this error message.

Any ideas?
0
I have a SQL table called ABC, that is being populated with data by a SSIS SQL task. It works fine for the original columns.

I added three new columns to the ABC table.

I modified the original SQL query that the SSIS SQL task is using. I first tested the new query in SSMS, and the three new columns of SQL table ABC has data.

However, when I save the SSIS package with new SQL query, those 3 column show as NULL. The correct values are not there, like when I run the same query SSMS.

Please advise.
0
Hello and thanks in advance for any help you can give me.

I have written a simple SSIS package in Visual Studio 8 that reads from MSSQL and outputs to Excel 2003.  The package has two dataflows each writing to a different worksheet in the same workbook.  The first dataflow works fine but the second fails with the following error:

[OLE DB Destination [344]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
[OLE DB Destination [344]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (357)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (357)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE DB Destination" (344) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (357). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

During development both flows worked, but now I cannot get the second to work at all.  It …
0
Cloud Class® Course: MCSA MCSE Windows Server 2012
LVL 12
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

HI Guys ,
i have issue in send mail task .
While creataing send mail task i want to configure the smpt server using variable i have created a variable in package level
and the variable type is string and i have gave the server name in the value for that variable.And i have mapped the variable in to smpt connection in
expression property. but still i get the error like
[Send Mail Task] Error: An error occurred with the following error message: "The connection "XXX" is not found. This error is thrown by Connections collection when the specific connection element is not found.
  System.Runtime.InteropServices.COMException: The connection "mail01.corp.suth.com" is not found. This error is thrown by Connections collection when the specific connection element is not found.
".

can any one help me for this.Thanks in advance
0
Thank you all in advance!

We would like to create a table in SQL from an Excel.

I've configured a WMI Event Watcher Task in SSIS that can start a data Flow when any file is dropped into the watch folder, however because the name of the excel files changes,  I'm having trouble configuring  the "Excel Source" component. Also, on occasion, the names of the columns may change in the excel file.

If we could import the dynamically named excel into sql with a static name as a table, we could probably then set up some stored procs to work the data over.

Just not sure how to get the excel file into SQL.

thank you all in advance!
0
Thank you all in advance!

We would like to create a table in SQL from an Excel.

I've configured a WMI Event Watcher Task in SSIS that can start a data Flow when any file is dropped into the watch folder, however because the name of the excel files changes,  I'm having trouble configuring  the "Excel Source" component. Also, on occasion, the names of the columns may change in the excel file.

If we could import the dynamically named excel into sql with a static name as a table, we could probably then set up some stored procs to work the data over.

Just not sure how to get the excel file into SQL.

thank you all in advance!
0
Hi, i'm need to know it's possible to create Live Data warehouse.
to be transfer data when adding new records or any changes immediately
0
How can I find a particular transformation in an huge SSIS project solution. I have a project that has lot of projects calling other child packages. I am new to this environment and when running the package from a job I am getting an error message that Source: xyz Execute SQL Task is failing.

Is there a shortcut to trace this transformation easily ?
0
Hi,

I'm using SQL SERVER 2008R2, Visual Studio 2008 for SSIS.

When the parent stored procedure usp_TEST_PARM  calls a child stored procedure and there’s an error in the child stored procedure, how do we get the child stored procedure to RETURN  a value so that SSIS would know that an error occurred in the child stored procedure (so that SSIS can send an alert email that says that an error happened in the child stored procedure and not the parent stored procedure usp_TEST_PARM  )?

GO

/****** Object:  StoredProcedure [dbo].[usp_TEST_PARM]    Script Date: 03/08/2018 16:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*

EXECUTE dbo.usp_TEST_PARM 'parm1'

*/

CREATE PROCEDURE [dbo].[usp_TEST_PARM]

@PASS_PARM as varchar(50)='Opps'

AS

SET NOCOUNT ON

SELECT [Field1]='Hello',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello2',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello3',[Field2]='Seconds'

RETURN 3

SET NOCOUNT OFF


GO


/*
to run it:

declare @var1  int
EXEC @var1 = dbo.usp_TEST_PARM 'parm1'
SELECT 'RETURN STATUS ' = @var1
GO
-------------------
RESULT:

Hello	parm1
Hello2	parm1
Hello3	Seconds

----------------------
RETURN STATUS 
3

*/

Open in new window

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.

Top Experts In
SSIS
<
Monthly
>