[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Hi,
Can you guide how can i connect to oracle database using ssis.

Thanks
0
New feature and membership benefit!
LVL 11
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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

  Attached is the SSIS 2012 package.
I have the Last Load Date task in it, in which the variable User::LastLoadDateasint,is supposed to cdapture the value LastLoadDate.
Somehow, it is not capturing it.
Can someone kindly help me with the fix?
0
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
Important Lessons on Recovering from Petya
LVL 11
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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
Hi,
I'm trying load fact table compare with DimSalesperson . Fact data coming for Different Database and server.The DimTables coming from Different Database and Server?
I wrote query to match but just wondering how create connection with 2 server and 2 database
Example

SELECT s.Person, c.SKSalesPerson
FROM   [DWServer].[Database1].dbo.dimSalesPerson c
      [DBServer].[Database1].dbo. FactSales s
WHERE  c.SalesPerson_id = s.SalesPerson_id

This servers and 2 phisical locations.(May be Virual servers)
Without using Link servers
0
http://blog.extreme-advice.com/ Backup Linked Server script in SQL Server by SSIS

Should I be receiving this error and what is the work around.  Thanks much!

I copied the code from the blog into the sql command.  When previewing I get the following error:

Error at data flow task ssis error code dts_e_oledberror.  An ole db error has occurred.  Error code 0x80004005. An ole db record is available.  The metadata could not be determined becuase the statement 'Exec (@SQL) contains dynamic SQL.  Consider using WITH RESULTS SETS clause to explicitly describe the results set.  

The code copied into the sql command
SET FMTONLY OFF
Declare @SQL Varchar(Max)
Set @SQL =
'Declare @LinkedServer Table ( LinkedServer Varchar(Max) )
Declare @status smallint,
@server sysname,
@srvid smallint,
@srvproduct nvarchar(128),
@allsetopt int,
@provider nvarchar(128),
@datasrc nvarchar(4000),
@location nvarchar(4000),
@provstr nvarchar(4000),
@catalog sysname,
@netname varchar(30),
@srvoption varchar(30),
@loclogin varchar(30),
@rmtlogin varchar(30),
@selfstatus smallint,
@rmtpass sysname,
@passwordtext nvarchar(128),
@i int,
@lsb tinyint,
@msb tinyint,
@tmp varbinary(256)

select @allsetopt=number from master.dbo.spt_values
where type = ''A'' and name = ''ALL SETTABLE OPTIONS''

declare d cursor for
SELECT srvid,srvstatus, srvname, srvproduct, providername, datasource, location, providerstring, catalog, srvnetname
from master..sysservers where srvid > 0 …
0
I thought this would be easy and I am able to do this with ease in TSQL, but I am struggling in SSIS. I have a flat file (.txt) that has a column named [DATE], this column puts the start_date and end_date together like this: "03/01/2017 - 04/01/2017". I am able to perform the conversion I need with a simple CAST in TSQL like this:
declare @test varchar (50) ='03/02/2017 - 04/01/2017'

select cast(left(@test,10) as smalldatetime) as Date_Start

select cast(right(@test,10) as smalldatetime) as Date_End

Open in new window


I have tried using the [derived column] and [data conversion] in SSIS with no luck. I keep getting this error when trying to insert into the OLE DB destination: "The value could not be converted because of a potential loss of data".

Please help experts....
0
SQL SERVER : Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64)
OS : Windows Server 2008 R2 Datacenter SP1 (X64)

Hi!

We have in acceptance a SSIS catalog where we found recently that we can’t deploy new projects, last successfully deployed project was 12-Sep-2017.
When we deploy a new project to the server only one symmetric key and one certificate is created for the project and when executing any package from this project we instantly get a result of ‘Unexpected Termination’. We don’t get any error message on the execution report from the catalog. The package is never executed.
If we deploy the same project on top of a previous existing project that had 2 symmetric keys and 2 certificates, the packages run successfully.
We detached the ssisdb and created a brand new catalog from scratch and we re-deployed all our projects (17 total) and only 2 of them created 2 keys and 2 certificates, the other deployments only created 1 object of each and gave the same ‘Unexpected Termination ‘ error.
Finally we undo the brand new catalog by re-attaching the previous catalog.  
The operating system error is :
The SSIS Execution Process could not write to the IS catalog: 
<server name>:SSISDB Error details: Cannot find the symmetric key 'MS_Enckey_Proj_Param_10', because it does not exist or you do not have permission.;
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
 at 

Open in new window

0
Hi Guys,

We have created one VBS file to convert the Excel file to CSV format and the given VBS file is being executed through Batch file.The batch file executes with the heop of SSIS Execute Process Task. Everything works fine through SSDT/BIDS but the package is getting hanged continuously if we try to run through SQL Job.

These are the code

 Batch File :
wscript S:\Batch\vbsExceltoCSV.vbs S:\Batch\Client_200817.xlsx S:\Batch\Client_200817.csv

VBS:
Set objArgs = WScript.Arguments
InputName = objArgs(0)
OutputName = objArgs(1)
Set objExcel = CreateObject("Excel.application")
objExcel.application.visible=false
objExcel.application.displayalerts=false
set objExcelBook = objExcel.Workbooks.Open(InputName)
objExcelBook.SaveAs OutputName, -4158
objExcel.Application.Quit
objExcel.Quit

Even when I try to execute the batch file from SQL Job (CmdExec) , facing same issue.

I have tried with both Service and proxy account but no luck.

Could you please help me to identify what could be the issue.

Regards,
Gopi K
0
hi,
I am getting the following error when.trying to load an sln file into Microsoft visual studio 2012
one or more projects in the solution were not loaded correctly. please see the output window for details.

there is no information displayed in the output window.
any help greatly appreciated,
thanks
0
Hi

I have inherited an old database which uses a MS Access Frontend to Import data into a SQL Backend via SSIS package .
It appears the connections to SSIS in the following Access DB Vb script fails as it only complies with TLS1.0 and not TLS1.2 as desired when using web based access.
Code:
cnn.ConnectionString = "Provider=SQLOLEDB;Data Source=MySQLData;Initial Catalog= MyProject;User ID=MySSISImport; Password= XXXXX;"
    cnn.Open

To resolve this to be TLS1.2 compliant should I be looking for
1- A security patch for SSIS
2- Re-write connection string an alternative way

Thanks
0
I have 2 different types of XML files called Install and Uninstall.

Install I have the XML file, generated the XSD and then ran the file through and it imports the data fine.

Now the uninstall one is very similar to the install, mostly the same columns 1-2 different, I have created it in exactly the same way, generate the XSD and then run my job but no data is transferred from the flat file source, there are no error messages and no information.

I am at a bit of a loss here as to why this could be happening. Does anyone have any ideas what could be causing this?

Looking at both XSD and XML files I cant see any structure problems.
0
Vote for the Most Valuable Expert
LVL 7
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Hello
I am on SSME 2012 and have three linked servers (1 oracle and 2 sql).
I am wanting to pull data from two of the linked servers (i oracle and 1 sql) into a temp table  add additional columns to the result with default entries and then insert  that table into another sql database.

So i have oracle1 that has  two entries i need  (entry1 and entry2), i have sql1 that has the remainder of the data i need but entry2 in oracle is the relationship in sql1 in one table but the info is spread across three tables in sql1.
The output would be hiding the relationship link:
oracle1.entry1, sql1.info1, sql1.info2,sql1.info3 .... sql1.info8, default column one with default text, default column two with default text, default column three with default text

This will then be inserted into sql2

I have played with openquery and select statements and can individually get the info from the 2 systems but it is the relationships and joins i am having issues with to pull out the final results in the query window.

Any guidance will be appreciated.

Paul.
0
Hi.  Do you know how I can translate the .vbs to vb.net for SSIS 2008?   There’s a lot of logic in the .vbs files so I am concerned about not translating it right.   Please provide specific examples of a .vbs and the end result SSIS .dtsx package so I can follow, not just guidelines.

I am using:
-  Visual Studio 2008
- SQL SERVER 2008R2
0
I need run the python py file into the ssis package using execute process task
0
In internet explorer i am able to access a https site and download a file. i have to use an username and password to access the site first.
However, when i enter the URL and credentials in SSIS using the HTTP connection managerand press the test connection button,  i get the message:

The remote server returned an error: (401) Unauthorized.

Any ideas why this is happening. The username and password are correct.

Can i use an https site in a http connection?

Any help appreciated.

Thanks
0
I have a requirement that I need to load an Excel file with multiple tabs into the SQL Server table.

The Excel file contains multiple tabs with each month of data in it. I should treat the current month tab like a daily month file and import the full tab each day as a truncate and reload.

Truncate and reload raw tables for current tab and last month tab.

How do we do dynamic tab variables for identifying ingestion in SSIS?

FYI.. TABS in the Excel file are named Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

These tabs are named at random - not in a sequence..

Can anyone please guide me?

FYI..I wanted to load just 2 months data at the same time not all months. For Instance,
This is August so i would like to load July and august data every day. I truncate the table and reload every day.
When i am in September then i should load Sep and Aug data.

Appreciate your response.
Here is the example how tabs are arranged.
Jan, Feb, Mar, Apr, May, Jun, Aug,Jul,  Sep, Oct, Nov, Dec.
0
Hi All,

I'm having an issue with SSIS 2012 Look up transformation . I'm querying 4.5 million records from source system. Then I will need to compare them with a destination table  (another 4.5 million records) to perform insert for new records and update for existing records.

while running this ssis package my c drive getting full.

i am using lookup full cache mode.

kindly help me to resolve this issue.



Thanks in advance.



Warm Regards

Ganesh Yerme
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.