Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Good day,

I have been asked to get involved with the installation and configuration of a SQL Server BI environment.

I am seeking some guidance for the deployment architecture for SQL Server, SSIS, SSAS, and SSRS as well as SSMS and Visual Studio.

To date, my experience with these products have had all products installed on a single server, with a fairly straightforward, default installation (and training modules a la AdventureWorks).

Given that I have been provided with a development server for DB (Windows 2012R2) and separate server for the apps tier (IIS), I am seeking information/clarification as to which products need to be installed on which server and in which order.

Added into this, is the requirement for SSIS to connect to an Oracle database leveraging Microsoft (Attunity) Connectors for Oracle.

QUESTION: Can you please confirm which of the following products should be installed on which server? (and references/links to the Manual(s) which I should be reading would also be appreciated. Any links to architecture diagrams depicting a similar architecture (with or without Attunity) would be a significant bonus.

MS SQL Server 2016
MS SQL Server Data Tools (2016), including
   - MS SQL Server Analytic Services (SSAS)
   - MS SQL Server Integration Services (SSIS)
   - MS SQL Server Reporting Services (SSRS)
MS SQL Server Management Studio
MS Visual Studio 2015
Microsoft (Attunity) Connectors for Oracle and Teradata (v4)
MS SQL Server …
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 a script component where I'm concactenating several strings then inserting the resultant string into a DT_NEXT field.  So I have something like this (s is the string).

if (!Row.chkenvironmentaldatapres_IsNull && !String.IsNullOrWhiteSpace(Row.chkenvironmentaldatapres.ToString()))
            s = s + "Environmental data pres: " + Row.chkenvironmentaldatapres.ToString() + Environment.NewLine;

        if (!Row.smokestatuscode_IsNull && !String.IsNullOrWhiteSpace(Row.smokestatuscode.ToString()))
            s = s + "Smoke status code: " + Row.smokestatuscode.ToString() + Environment.NewLine;

        if (!Row.txttobaccocessdiscuss_IsNull && !String.IsNullOrWhiteSpace(Row.txttobaccocessdiscuss.ToString()))
            s = s + "Tobacco cessation discuss: " + Row.txttobaccocessdiscuss.ToString() + Environment.NewLine;


        Row.SH.AddBlobData(GetBytes(s));

    }

    private byte[] GetBytes(string str)
    {
        byte[] bytes = new byte[str.Length * sizeof(char)];
        System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
        return bytes;
    }

}

Open in new window


The problem is the Environment.Newline isn't working.  My text shows up in the application looking like this:

Categorye One:  Patient Presents with this
Category Two: Patient Presents with this&#x0D

I should be seeing:
Category One:  Patient presents with this

Category Two:  Patient presents with this

What should I be using besides Environment.Newline?

James
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
Hello all.

I have a stored procedure that I need to schedule to run on the 30th of the month.

The SP needs to have a Start Date of  Previous Month/15/YYYY and End Date Current Month/14/YYYY.  
I was thinking I could just use MONTH(DateAdd(MM,1, getDate()) and then append the rest of the date here.

So for example last month I need to have the date range to be 11/15/2017 to 12/14/2017.  This month I need the range to be 12/15/2017 to 01/14/2018.  

How do I get the years to change when appropriate?

Using MS SQL 2014

Thanks,
Rodger
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
Hi everyone, I wondered if someone could help me please, I am looking to start a new online business selling videos to local business owners but mainly selling videos that my customers can create / customise / edit and buy online kind of on autopilot.

I would like to create videos in formats for Facebook headers, instagram, everyday marketing videos, the kind where customers can upload their own images and videos to my templates if that makes sense, perhaps integrate stock photo and video options.

I was wanting to offer options for usual videos as well as facebook headers and instagram, providing templates and customers either upload their own images or video clips or could perhaps add direct from pixabay etc.

I note that there is various open source software available but I need to integrate and set it up inside my website.

Thanks for your time and I look forward to hearing from you.
0
I need to automate unzipping multiple .zip files to different folders using 7 zip.  For example, I have 4 .zip files in one folder and need to unzip them into their own folders, not in to one.  Ideally, I would like to put the command or script into an SSIS package.
Thank you.
0
Receive 1:1 tech help
LVL 11
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

PlEASE FORGIVE.  I am very desperately looking how to ask this eloquently. Not doing to good here.

In SSIS, if you have an incoming dataset that has multiple records for the same PK (like intraday records in a daily processing method), how do I tell the SCD Wizard to expect that?

in my case, even though all of my fields are set to "Historical", they all appear as new, because they are all coming in all at the same time.

    Example data:
    PK, insertion depth, drill depth, bore depth
    1, 1000, 1020, 1022
    1, 1025, 1222, 1233
    1, 1100, 1321, 1333
    2, 1233, 1255, 1022
    2, 2222, 3333, 3333
    3, 1010, 900, 885

Open in new window


I get this in one report.  They all show up as new in base load.  I have a create_dt, and end_dt as my start and stop timestamps.  create_dt has system::startdate for the variable.  How do I get the SCD stage in SSIS to recognize these, and add the timestamps as necessary, whether or not a record in the target table has that PK already?

Thanks.
0
We receive log files via email which we need to save to disk.  Our email server is Office365.  We want to have the emails saved to text files on a specific network folder.  We need this job to run twice every hour .  What is the best way to achieve this?  We are ultimately expecting to have this run as part of an SSIS package
0
I am looking for advise what would be the best approach for storing history of ETL load. The way ETL works is I am receiving flat file and it is loaded to staging area and from staging area to a production table and to a view
What would be the best approach in case I got duplicate file and load it I do not want that to happen
Thanks
Robert
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
I have a request of how to write a 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 that pulls using 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 dynamic 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
 

Author Comment

by:JOELL MERRITT
Oh ok !   Sorry!  :)
0
 
LVL 26

Expert Comment

by:Brian B
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
Hi,
i have stg_table

sin    lcode
123    01
123    02
123   03

join it with d_code
d_code  desc               d_code_id
-99         unknown         -99
01           usa                   2
02           ca                    3

when i join them using left outer

i get results like below

sin       lcode  d_code_id
123    01            2
123    02            3
123   03            null


i want null to be -99 i try to do in expression isnull(d_code_id) =-99 it is not liking it
0
Hi,
Can you guide how can i connect to oracle database using ssis.

Thanks
0
Get your problem seen by more experts
LVL 11
Get your problem seen by more experts

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

Hi,
I have dimension table with identity row dim_id ,
I want to add exception rows in dimensions like
-99 invalid
-98 unknown

how can i do that using ssis .
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
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
I have a got a list of files in a folder text file with names like this:
baseFile_03_17_2017 .txt
baseFile_04_17_2017  .txt
baseFile_05_17_2017  .txt

Keep in mind>> there is no date column within each the text file.

 I want to a do a SSIS (SQL Server Integration Services)  loop where I loop through each file and insert it into a stage table which has an extra empty “Reportdate” column at the very end.

The SSIS job then gets the date from the file name and inserts it into the very last “ReportDate” column in that stage table


How do I do that?

Thanks
0
In C# for SSIS, how can I create a create a 'Files Remaining' counter?

I have a function that gets the current number of files in a directory and stores that value in a variable.  I have code that cleans and processes file data in a foreach loop and I would like the file remaining counter to interval down each time it starts a new file.

The function code to get the number of files in the directory is below:

// LOGIC TO COUNT THE NUMBER OF ROWS IN A FILE
        public static long CountLines(string filename)
        {
            long result = 0;
            using (var input = File.OpenText(filename))
            {
                while (input.ReadLine() != null)
                {
                    ++result;
                }
            }
            return result;
        }

Open in new window


and I call or show the variable in the Dts Output by using

            // COUNT THE NUMBER OF FILES IN THE DROPOFF DIRECTORY
            long filecount = Directory.GetFiles(dropfolder).Length;
            Dts.Events.FireInformation(3, "File Count", "Number of files to be processed " + filecount.ToString("#,##0") + " files.", "", 0, ref fireAgain);

Open in new window


Thank you!
0
Hi,

I'm using SQL SERVER 2008R2.

I have a STORED PROCEDURE that imports a text file using a BULK INSERT into a real temporary table #table1.

Next, a SELECT statement  parses the strings from #table1 into variables then the the whole record gets INSERTED into tbl_destination.

PROBLEM: During parsing of the string, there are two scenarios that cause an error.
1. Invalid DATE conversions
2. Invalid NUMBER conversions

I would like to be able to catch these two invalid scenarios AND move them to another table tblExceptions
BEFORE the SELECT statement  parses the strings from #table1 into variables  and errors.

Please provide example tables and code of how to achieve this.

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.