[Webinar] Streamline your web hosting managementRegister Today

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 a blocking report (Blocking Detected On Production Server), Can anybody please help me understand that?

Here are the details:-

Session_id || Status || blocking_session_id || wait_type || wait_resource || WaitSec || cpu_time || Logical_reads || reads || writes

156 || Suspended || 137 || LCK_M_IS || OBJECT: 18:925246351:4 || 172.660000 || 7 || 204 || 66 || 0 ||

Anything more about this will be very helpful.

Thanks in Advance.
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.

I have a unique situation where my source system is Oracle, the Oracle DBAs (ODBAs) are using Golden Gate to push data to a replica that is also Oracle. The SQL Server (SDBAs) want to ingest the data from the Oracle Replica, and if we could get transactional replication to work, that would just be fantastic. I have read through some MS KBs that it is possible to have an Oracle source as the replication publisher. I wonder though if this would work knowing the Publication source is a golden gate replica.

I know with some Change Data Capture (CDC) tools, they actually use the replication engine of the source and target system. So enabling SQL Server replication on top of that does not always work as advertised.

The trick here is we are trying to not pay for another set of GG licenses if possible.

Are there any experts out there who have done this in the past?
1
When importing a flat file I get an error because one of the values in the file is negative number?  I tried a bunch of the different datatypes and a still get the problem. Any clue what the issue is?
0
query issue

select * from city where population >= '100000' and NAME='USA';

why above query is not resulting any results while trying on ms sql server

please advise
0
Hi,

I'm using SSIS 2008.

I have an 1 SSIS package that has 4 different SEQUENCE CONTAINERS.

My dtexec passes a parameter to the SSIS to tell it which SEQUENCE CONTAINER to run.

QUESTION: Can I have 4 dtexec running the same SSIS package at the same because they each run a different SEQUENCE CONTAINER?

Thanks.
0
I am using Visual Studio to create a SSIS package to convert a SQL file into a Pipe Delimited text file without the header row.
I have tried several times to skip the header rows.  I set the number to 1 on the general screen  for Header Rows to Skip and enabled the Column Name in the first data row.

I still get the row headers.

What am I doing wrong?

Thanks

Glen

Glen
0
Hi All

I'm trying to understand why I cannot see Integration in msdb.dbo.syscategories. The SQL instance I'm running is MS SQL 2016 Standard SP1, version details below

Microsoft SQL Server Management Studio                                    13.0.16000.28
Microsoft Analysis Services Client Tools                                            13.0.1700.441
Microsoft Data Access Components (MDAC)                                    10.0.14393.0
Microsoft MSXML                                                                                     3.0 6.0
Microsoft Internet Explorer                                                             9.11.14393.0
Microsoft .NET Framework                                                             4.0.30319.42000
Operating System                                                                             6.3.14393

In the syscategories I can see 22 items, see below

name
[Uncategorized (Local)]
[Uncategorized (Multi-Server)]
[Uncategorized]
[Uncategorized]
Data Collector
Database Engine Tuning Advisor
Database Maintenance
Full-Text
Jobs from MSX
Log Shipping
REPL-Alert Response
REPL-Checkup
REPL-Distribution
REPL-Distribution Cleanup
REPL-History Cleanup
Replication
REPL-LogReader
REPL-Merge
REPL-QueueReader
REPL-Snapshot
REPL-Subscription Cleanup
Reports

Open in new window


In the SQL Object Explorer, I can see Integration Services Catalogs and the Integrated Services is enabled as a feature whilst running MS SQL 2016 Setup (see SQL Features.pdf).

What I'm trying to do here is create a SQL Job and select Integration in the Category (see Category List). The Server OS is Windows Server 2016 Datacenter. Any help is greatly appreciated!

StevieSQL-Features.pdfCategory-List.pdf
0
In my SQL SSIS package, I have exported some data to file.

I want to export to a file with following filename format: filename_YYYYMMDD_hhmmss.txt

In my expression builder, it type in the following:
"C:\\BlueCreek Files\\Account\\AmesAccount"+"_"+(DT_STR, 50, 1252) Getdate()+".txt"

Result are:

C:\BlueCreek Files\Account\AmesAccount_2018-01-23 13:03:21.122000000.txt

This is close, but how can I get it in the exact format I want?
0
What does this error mean and how does it get resolved?

An error occurred while assigning a value to variable "TruncateLength": "Single Row result set is specified, but no rows were returned."

I am trying to load data using a SSIS package and receive the error above. Does it have to do with data in the files I am trying to load (for example some of the data needs to shortened in length or a comma) or is it something wrong with the SSIS package? I have two types of files and mapping instructions for each type I have four different files with regards to the data for each type. The same error comes up for each file. This leads me believe it is not with data, but with the SSIS package itself.  

Please let me know if any additional information is needed.
0
Hi all,
is there a documented process to do replication with log shipping where the only thing available to the destination database is the log files?  If not I will have to setup an SSIS package to do restores of the log files to the destination database, which seems problematic?

thanks,
john.
0
Take Control of Web Hosting For Your Clients
LVL 11
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.

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
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
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
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
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
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,
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
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
Upgrade your Question Security!
LVL 11
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

I am currently building am data warehouse but I am finding that the reload of the fact table is taking longer than the business finds acceptable. I have been emptying out the fact table on every incremental load and repopulating it. So on each load, we start again from scratch. It worth noting that when a row needs to be changed in the source, a new row is added to the source. so type 2 SCD do not seem to be worth the effort.

Is there any way that we do not have to empty and reload the fact table?

Many thanks
0
Hi EE,

Back again with the lookup issue in SSIS,
SSIS lookup failing with the following errors
[ClientRefLkup [296]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "ClientRefLkup" failed because error code 0xC020901E occurred, and the error row disposition on "ClientRefLkup.Outputs[Lookup Match Output]" 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 "ClientRefLkup" (296) failed with error code 0xC0209029 while processing input "Lookup Input" (310). 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.

[Booking Source [2]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Booking Source returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages 

Open in new window

0
Hi EE,

I am trying to use SSIS lookup but getting the error on second lookup. I have tested with one lookup and it works fine, but as soon as i add up the second lookup it fails and throws errors.

SSIS error
The four error i get are below and i couldn't find any solution for this.
[SupplierLkup [401]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "SupplierLkup" failed because error code 0xC020901E occurred, and the error row disposition on "SupplierLkup.Outputs[Lookup Match Output]" 
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 "SupplierLkup" (401) failed with error code 0xC0209029 while processing input "Lookup Input" (415). 
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.

[Booking Source [2]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on Booking Source returned error code 0xC02020C4.  The component returned a failure 

Open in new window

0
Hi,

I have a very basic SSIS package which copies records from a SQL database table to an Oracle database table.  Although it executes correctly without error, there is a persistent 'WARNING' icon on the lookup component.  The warning is:

[Lookup [2]] Warning: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

I have resolved this same warning on the OLE DB source and destination components by setting the property "AlwaysUseDefaultCodePage" to TRUE but the LOOKUP component does not seem to have this property.

My question is:
Is there something I can do to resolve this warning on the LOOKUP component in the data flow or is it ok to ignore it as the package executes successfully?

Thank you for your help! (I am new to SSIS...)  I am developing the package using Visual Studio 2010 Shell

This is a screenshot of the data flow so you can see the warning is on the lookup component:
SSIS Lookup Component Warning screenshot
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.