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,

I have a scenario where i have sql code that i need to run in one server. I can only read and execute query and cannot create a base table. I can create temp tables. I have many such sqls to run. I want to output result sets of many sqls to their respective  flat file destination.  Select col1, col2 into #tble1 from A. What is the best approach or options?
0
Get expert help—faster!
LVL 12
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

I have a need where I need to retrieve CSV files from an external source which will contain either updated or new records for tables in my SQL database (one CSV file per table).

This needs to be a job that will open each file (file name will be the table name to be updated or added to) in the folder, update existing records or add new records, depending on what's in the file (could be both, or one or the other).

Is SSIS the best approach for this (this is where I'm leaning)? If so, how would one go about automating this process? Some things to note:

1. The files could be different every day since not all tables may or may be updated or added to every day.
2. There may be no files in the folder on any given day.
3. Obviously, since each CSV is related to a different table, the column names will be different for each CSV (which would probably affect column mappings in the Data Flow of the SSIS package), and I really don't want to create a SSIS package for every single possible table in the database. That'd be 100s of SSIS packages.

Any help would be appreciated, especially on how to determine if a record in the CSV is new or if it's an existing record. If it's an existing record, all columns will be updated in the table based on what's in the CSV.

Please let me know if you need more details from me on this.

I'd also be open to purchasing existing software if this becomes too cumbersome.

Thanks in advance!
0
I have following Dim Tables
DimCustomer,DimSalesPerson..ect

FactTable :FactInvoiceDetails

My question is :

DimCustomer and DimSalesPerson having SiteID
SiteID are United Kigdom,Germany,France,USA

Dimcustomer and FactTable (FactInvoiceDetails) connected with SKCustomerKey
DimSalesPerson and FactTable (FactInvoiceDetails) connected with SKSalesPersonKey

I need to know How to connect Dimsite with DimCustomer and DimSalesPerson

But Dimsite key is connected FactInvoiceDetails not DimCustomer and DimSalesPerson
0
I had this question after viewing Problem saving SSIS Package to SQL Server.

Hi,
I am using an shared mssql server for my own projects. I am not an admin on this server, but I will want to fix my problem from them, but firstly I need to define my needs clearly.
I have a database in this server which assigned to me. I want to create SSIS packages (via import wizard) and then schedule jobs for refreshing this packages.

My problems:
1- When I try to save my import process as a SSIS package. I take this error: The attempted operation is not supported with this database version.
2- I can't see 'SQL Server Agent' (I think it is about my permissions)

Can you help me especially about first problem?
0
ISSUE :

Cannot process the SSIS package ( to process Cube) from SQL Agent Job  but the package is processed successfully without any error from  SQL Server Data Tools (Visual Studio) when processed manually .  Environment used is WINDOWS 2012 R2 && SQL SERVER 2016.

OBSERVATION:

1.Same SSIS Package mentioned above( to process Cube) is processed successfully without any error from  SQL Server Agent .  Environment used is WINDOWS 2016 && SQL SERVER 2016.  It seems that problem may be with WINDOWS 2012 R2.

Error Message :

Executed as user: JDADELIVERS\_dbadmin. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.1601.5 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  3:31:45 AM  Error: 2018-02-01 03:59:34.01     Code: 0x00000001     Source: Connect AS Server      Description: Exception has been thrown by the target of an invocation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).Started:  3:31:45 AM  Finished: 3:59:34 AM  Elapsed:  1668.25 seconds.  Process Exit Code 1.  The step failed.


Please help me in identifying the problem  and the root cause of the error. . Will appreciate your help in this regard.

Thanks,
SRK.
0
What is the best way to upgrade 2008 SSIS packages to 2106 SP1

All script tasks disappear during regular upgrade . Please share some experience

thanks
Julia
0
How do I track the last start_lsn operation change if the PK field has an update?  What I have been doing is partitioning on the start_lsn ordering by the SeqVal desc and getting rownum of 1 for the set.  However, it seems like the delete happens last so I miss the inserted record which is odd.  I am using ssis and I get all my ct changes into a staging table, I then need to make sure I handle the crud operations right against the destination table.  Thanks all for any suggestions.
0
Hi

Can you please help me write a SSIS packages?

SSIS package #1

Backup SSIS packages (Resides on PC-WPAP101)
1.      Create SSIS packages to backup each of the common databases (YYY, XXX, ZZZ) to F drive on PC-WPAP201 (remote server).
2.      In data flow of SSIS package, after backup is complete set a marker.  This just will create an sp object for the restore job to look for.  Backup file should have a static name and overwrite any existing backup file with the same name in that location.

SSIS package #2 (Resides on FC-WPAP301)
1.      Look for marker on source server (PC-WPAP101) for appropriate database.
2.      When marker found, remove marker so we don’t have two instances of the job running concurrently.
3.      Restore database from appropriate database backup file to database on FC-WPAP301.


I am using MS SQL 2014

Please help, M
0
Hi,
I'm getting following Error Message

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimCustomer', Column: 'siteID', Value: '1'; Table: 'dbo_DimCustomer', Column: 'city', Value: ''. The attribute is 'City'.

Example
SiteID - 1,2,3
City - Key Atrribute , SiteID ,City and Name conversion "City Label" (County+ city)
Country  Key Atrribute , SiteID ,Country and Name conversion "Country Label" (County)
0
I would like to add records to an existing table but only with data in selected columns leaving the rest un-populated.  Of the columns that are not included are ones that are specified as non-null, including some numeric fields.

In the past, I have been able to do this by using the Tasks -> Import Data wizard in SSMS.  Suddenly, this has stopped working giving an error message about trying to put NULL data into a non-null column (see the error message in the 2nd image regarding the ADCOST column in the dbo.ADCOSTS table.   The columns in green (commented out) are just my notes for which columns to include and are not part of any commands.

Something has changed that now prevents me from doing something that I had been for several years.  Either that, or I've changed something, but I can't find what that would be.

I've attached a couple of images that might help.  It shows the table to be appended and the columns that I wish to include.
IMG_6288.JPG
IMG_6290.JPG
0
Take Control of Web Hosting For Your Clients
LVL 12
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Hello,

I am working on a project to migrate several large access databases to a MSSQL 2016 database.  I've completed the migration and created all the stored procedures and necessary views as well as tables.  However, approximately 50 files will have to be imported into the database on a weekly basis. I don't know much about SSIS but have used the SQL Server Import and Export data tool to import the data.   I would like t use SSIS to import these files.   However, I don't know that much about SSIS.  What books would anyone recommend for learning SSIS. I've had quite a bit of database experience (MS Access and some MSSQL, including sql and T-Sql but minimal SSIS.  Thanks

Juan
0
Hello,

We want to insert clients information into salseforce.
We have all the information in an SQL Server database.
We want to use ssis to update the data in SalesForce in a daily basis.

We don't want to purshase any toolkit or ready to use component.

Can I find any  step by step tutorial to do so ? I came across some tutorials suggesting webservice option, but It didn't work in my case because of my poor knowlege in Webservices.

Any help is apprecited.

Thanks
Salma
0
I have 56 SSIS reports that are setup to use the Excel connection manager to pick up file extracts that are xls file format.
The file format of the extracts were changed to xlsx format and the reports have not updated with current data since.
I inherited these reports and time does not permit re-writing and redeploying these reports.
Any help with this issues will be much appreciated.
0
Importing XML files created everyday within a folder and subfolder created at 4:00 PM everyday Mon-Fri, within the folder is an XML file  with name convention exported 4:03PM which will need to be loaded to SQL table

 folder path : Z:\background-processarea\Outbound
 Subfolders Paths:
                                      Name                                                            DateModified                               Type
Z:\background-processarea\Outbound\BGP_1413656            1/8/2018 4:00PM                     File folder      
            Xx exported                                                                           1/8/2018 4:03PM                       XML
            Xx recorder-BGP-out-210307                                             1/8/2018 4:00PM                       XML  
Z:\background-processarea\Outbound\BGP_1412472            1/5/2018 7:00PM                     File folder
          Xx exported                                                                           1/8/2018 4:03PM                         XML
          Xx recorder-BGP-out-207020                                             1/8/2018 4:00PM                         XML  
Z:\background-processarea\Outbound\BGP_1412471            1/5/2018 4:00PM                     File folder
            Xx exported                                                                           1/8/2018 4:03PM                       XML
            Xx recorder-BGP-out-206848          …
0
I am trying to copy a database using SSIS Transfer SQL Server Objects Task.


    [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "An exception occurred in SMO.".
    [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Value cannot be null.
    Parameter name: server".

Open in new window


I hit test connection and prove that I have the right server name and password.  Configuration page
0
I have an Excel vba app that I have used for years, which builds reports from data it retrieves from a Sql Server warehouse.  It has run tens of thousands of reports.

Now I'm pulling the core vb code over to SSIS and building a script to do the same operation.  It runs well overall but one routine is very slow.  It works, but is super slow on big files, and I have some with hundreds of thousands of rows..

I am using VB script 2012, and it is essentially the exact same subroutine as the vba app, and can't figure out why it is so much slower.  I am turning off screen refresh and calculations, so that isn't the issue.

Here is the core loop that is slower.  I'm alternating background color on a column value, so when the value changes, it toggles back to the background color, or turns it off, until it hits a blank value.

Can anybody guess why this is slower in SSIS? I am referencing Microsoft.Office.Interop.Excel...

                Do
                    If UCase(.Cells(iRow, iTriggerCol).Value) <> UCase(.Cells(iStartRow, iTriggerCol).Value) Then
                        'change, so switch
                        bHasChanged = True
                        oRng = .Range(oWS.Cells(iStartRow, 1), oWS.Cells(iRow - 1, iLastCol))
                        If bColorMe Then
                            oRng.Interior.Color = 16772300  'light blue
                            bColorMe = False
                        Else
                            bColorMe = True
        

Open in new window

0
I have a request of how to write an Execute SQL task in SSIS.   More of a fail safe for when a vendor's file does not make it to our system.    This is causing the below issue.

I have a SQL job Step 1 that successfully uploads a CSV File from a WINSCP Windows task script to retrieve the latest CSV file from a SFTP site.   Then the SQL job Step 2 sends the file to the SSIS package for loading into SQL.   The problem occurs when the SFTP site does not receive a new file.  
Step 1 does not fail because it successfully retrieved the latest file, however, Step 2 using the package fails because the file that was pulled already exists from the previous pull.   I need a way to check in the SSIS package to see if the dynamically named CSV file exists before processing.    I am very green to writing variables and I have never used the Script task command.  Is there a way to do this check before my package kicks off the For each file task by using the Execute SQL task  ?    

As a side note, I do not have permissions to delete older files off of the SFTP site.    And, even though the SQL Job Step 2 says it failed, it still executes the CSV file so now I have duplicate records for the same day.

Please advise exact steps as I am a beginner.
0
Hello Everyone,
 
I am working on SSIS 2012, I have 3 projects under a solution, Project 1, Project 2, Project 3 and all these projects(SSIS Packages) are in server1. I have Master Package in Project 2 from which I am calling packages in Project1 and Project3 in execute package task referring file system, So everything is working good till here, all my packages are executing fine. but my requirement is to implement Rollback transaction support, so if any package fails while executing the master package everything should be rolled back to the starting point. To achieve this I have set the package level TransactionOption to ‘Required’ and all other tasks inside the master package to supported(Default), My packages involves one more server(server2) also, I made sure that DTC is running in both the servers, I also made sure that 'Network DTC Access is enabled, 'allow remote cleints' check box is checked, 'Allow Inbound' and 'Allow Outbound' checkbox is checked and 'mutual authentication requires' radio buttion is selected, I followed this in both the servers. Also, I made sure that DTC firewall is enabled in both the servers i.e. under 'Windows Firewall with Advanced Security' both inbound and outbound DTC(TCP-in) and DTC(TCP-out) is enabled in both the servers, after doing all this, I am still facing the above error, can you please help me where I am going wrong?
0
First let me say that I am very new to SSIS and XML.........I have created a SSIS package that takes XML data in (it contains main data, and some attachment data), splits this data and puts it into Excel files, picture is attached.  During this processing it places the main data in one table, and the attachment data in another table. The 'separation' table contains a Guidid, SSN, claimeffdate, claimnum, lastname, firstname, middleinitial, suffix and some other information.  The attachments table comes in with a description, type of document, unique attachment id, actionable attachment, attachmentsize and the attachmentdata.

As I am transforming this data, I need to put the GUIDID on the SQL table with the attachment data.  I have two people with attachments, and the other 4 do not have attachments.  I'm using a query that runs before and after the 'Shred and Stage in my foreach loop container that is like:
declare @ID as decimal(38,0)

set @ID = (Select Top 1 StateGUID from SIDES.dbo.StateSeparationInformation order by StateGuid desc)

update sides.dbo.[INC-Attachment]
set StateGUID = @ID
where StateGUID is null


The query executes fine, and the first person gets the correct GUID on all their attachments.  The second person that should get a GUID for an attachment does not instead the GUID for another person gets  put on that attachment.

The query works great if everyone has attachments, just not when some have attachments and some do not.

I added it …
0
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

We are starting upgrade project to upgrade from SQL Server 2008 to SQL Server 2016

What is the best/ fastest way to upgrade all SSIS packages and SSRS reports ?

thanks
1
Hi there,

I am trying to use merge join in SSIS but get the following error message. Can you please help
Merge Join Error
0
I am trying to write in expression builder a script that gets what number of day it is in the current year.  I've tried this: DATEDIFF("DAY","1-1-"+(DT_WSTR,4,1252) datepart("yyyy",(GETDATE()))  + ,GETDATE()) and I am getting conversion errors.  Does anyone have an idea on what I can do differently.
0
I'm not able to connect to the attached 97-2003 xls file using SSIS on my development machine.

We're running this on a Windows 10 x64 machine in Visual Studio 2015.  I've installed 64-bit Access 2010 driver and no luck  Removed those drivers and installed 32-bit drivers same result.  Any advise greatly appreciated!

error messageBook1.xls
0
I need an output file(s) that selects from a single Intake table/template in our EHR from a single most recent visit that equals @CurrentDate. However this single row return also needs to include all the meds, allergies and problems they have. Can this be done as a single output file? Once I have this I need to utilize it in an SSIS package to loop through all patients that meet the criteria.


I can include detail of the tables but for now just not sure how to even go about this. A UNION, or nested select statements? Not sure.  This can be done as separate files as well that breaks out meds, allergies etc. so that each one saves to a csv file with the unique patient identifier tying them together. Maybe a temp table could work as well.

Appreciate any help.
0
Expert Advice (Kent Olsen)
me to use for adding Frieght to FactInvoiceDetails table

CREATE TABLE DimItemType (
  id   integer IDENTITY,
  TypeName varchar(30)
);

And populate it with the possible entry types:

INSERT INTO DimItemType (TypeName) SELECT 'DATA'
INSERT INTO DimItemType (TypeName) SELECT 'FREIGHT'
INSERT INTO DimItemType (TypeName) SELECT 'SALES TAX'
etc...

Add the column ItemTypeID (integer) to the FactInvoiceLine table.
When an item is stored into FactInvoiceLine, set the ItemTypeID value according to the data type.

My questions is :

how to combine FactInvoiceDetails Table and DimItemType and i have created view to transfer to FactInvoiceheader Table through
SSIS. (OLEDB source)

01. Following View is correct? or any issue
02.Then how to add the ProductID for the FactInvoiceDetail Table, becuase when i'm adding extraline for
Frieght and Tax
CREATE VIEW DW_FactInvoiceDetails
AS

SELECT invhdr.invNumber,0 AS InvLine,0 AS Qty,invhdr.FREIGHT_AMOUNT AS price,2 AS ItemTypeID --FRIEGhT is 2
FROM Invoiceheader invhdr
WHERE FREIGHT_AMOUNT <>0 --May be - also possible

UNION ALL

SELECT invLine.invNumber,invLine.InvLine,invLine.Qty,invLine.price,1 AS ItemTypeID --DATA is 1
FROM InvoiceLine invLine


UNION ALL

SELECT invLine.invNumber,NULL AS InvLine,0,invLine.Sales_Tax AS price,3 AS ItemTypeID --SALES TAX is 3
FROM Invoiceheader invLine
WHERE invLine.Sales_Tax NOT IN(0)

GO

Open in new window

Any comments greatly appriciated.

Many thanks
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.