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  am looking for the best way to use SSIS to export contact data from SQL Server 2016 to an Outlook 2016 contact folder.  The contact data come from a view with over 5000 records.
0
Cloud Class® Course: Ruby Fundamentals
LVL 12
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

The last time I used SSIS was with SQL Server 2008, and pretty straightforward ETL tasks with some conditional branching, etc. I am getting into SQL Server 2017/Visual Studio 2017 and maintaining some existing packages.
I have come across some code in script tasks and having some difficulty finding online documentation. I am finding the properties LockForOneRead(), LockForOneWrite() on VariableContainer() and LockForRead() and LockForWrite() on Variables and an Unlock() property. I finally did find something that gives me a little insight, but still a little unsure and hopefully an expert can give me a little more insight.
  1. If the idea is to lock a variable so competing processes within the package aren't accessing it at the same time, what happens if process A locks a variable and process B tries to lock the same variable.
  2. What happens if I forget to unlock the Variables collection in my task? Will it be unlocked when the script task goes out of scope?
I've read that you can do implicit locking by using the syntax Dts.Variables["User::var_name"].Value = "some value"; so I guess these questions refer to doing explicit locking and unlocking.
It seems to me that unless you lock a variable in a very long running script task (most of the code I've seen locks, gets value, sets another string value, etc., or updates a table with a parameter value, etc., then unlocks), the likelihood of two processes accessing the same variable(s) is …
0
i'm looking for a way to web scrape a simple web page by taking a csv list as my parameter and retrieving the html table on the next page through SSIS
The webpage is http://p2web.energy.dla.mil/pls/p2wp/dfsc_pkg.df_contract

so in the contract textbox i would put contract number item in my csv and retrieve the following html element

table
not wanting to use Zappysys. wondering if there are other tools out there that are free.
0
Hi Expert,
I need to create Degenerate Dimensions Table? Can any one show me how to do that?
0
Creating an SSIS app with VS 2015.  Finally have the pieces connected.  When I run it I receive the below error.
===================================

Failed to start project (Microsoft Visual Studio)

===================================

Error starting debugging. (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
Program Location:

   at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.LaunchVsDebugger(IVsDebugger iVsDebugger, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

===================================

Unable to 

Open in new window

0
I am looking for an option of Active Directory Authentication in SSIS Connection Manager. Is there any such feature available? I can only see Windows and SQL Server Authentication as of now.
0
I am trying to schedule refresh my data source in tableau online and I am getting a connection failure error. I talked with Tableau support to see if it's anything I did wrong in tableau but they said it's SQL Server issue. I can connect to SQL server using Windows Authentication and create reports and can publish them . when i publish them the server name is coming as localhost. So, I tried to do SQL Server Authentication and used username: sa and password and logged in Tableau Desktop and published the data source but the scheduling doesn't work and gives me an error of connection failure again. So i tried looking at the connections in tableau online. Tried localhost, username sa and when i press test connection it gives an error couldn't found the server.
0
I'll start by confessing that I am unfortunately VERY new to SQL and a bit out of my depth, so your help would be massively appreciated.

I'm trying to run an SSIS job which up until recently has been working fine.

After about 15 mins the job fails and I receive

"Product Sales With View Fact:Error: SSIS Error Code DTS_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Transaction (process ID 72)Was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Open in new window


So I rerun the job and the same thing happens over and over.

I have had a look for any running jobs (I assume I checked correctly...) and cannot find anything.

I assume something is stuck in a locked state from where the job was previously running and had to be stopped (via job activity monitor) but I haven't got a Scooby where to go from here.

We are using MS SQL 2014.

I have been researching deadlocks but as I mentioned i'm new to SQL and i'm not getting very far
If anyone has any specific ideas or advice, that would be amazing.

Cheers
0
I have an ssis project that works fine
It pulls in a specific file from a folder
C:\EW\FTP\InboundFiles.Inbound.xls

How do I modify the data source so that it pulls in any file that ends in xls regardless of the actual name?

There will only ever be one file in the folder.
0
My SSIS package needs to run on the last working day of the month, so I have created a lookup table that returns a row when the systemdate matches the calculated month end date which returns 1 row. When it is not month end, 0 row is returned.

With this I have created a variable (on success) to run the other packages.
However, I need suggestions on how to implement ending the execution without errors when the condition is not met (as the job will be scheduled to run daily).

See attachment for info.
Thank you
MonthEndFlag.png
0
Cloud Class® Course: CompTIA Healthcare IT Tech
LVL 12
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Hi, I am executing a stored procedure using an OLE DB Source inside a data flow task.
The SP contains 3 global temp tables (e.g, ##temptable1) but the task returns an error as it cannot find the target table in the database.
As a test, I have changed the SP to use CTE but the query runs too long and therefore need temp tables.

Please advise how can I overcome this by using global temp tables?
Let me know if further information is needed.

Thank you
0
I am working on a SSIS package where we are using a 3 rd party tool to connect to secured(Banking) FTP site and downloading one file at a time by passing a input variable. I am generating the variable value through a SQL task. As long as i keep passing a value other than 0 I have to keep looping and executing the 3party component.

How can I accomplish this?  whats the best way.. should I use forLoop  or For each Loop?  Can you please let me know how I can accomplish this. Fyi.. I am not good at script tasks, it would good if can use any other transformations other than Script tasks
0
I have a series of SSIS projects that run automatically each morning. Part of what they do is attempt to standardize addresses and retrieve latitude and longitude values from a master table. This master tables does get updated every few months. My question is this. Is it possible within SSIS to detect if a file exists in a folder and if it does proceed with the import and when the import is done to delete this file? I don't want the project to run on the same data morning after morning but only when an updated file is dropped into this folder. This input file is an Excel spreadsheet and the destination is a SQL table.
0
Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "OLEDB_SRC" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ProjectPPE_LoadWorkdayData:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80004005  Description: "ORA-12154: TNS:could not resolve the connect identifier specified
0
I have several SSIS projects that have run for a while with no issues. They read a csv file and insert data into SQL tables. Recently I have noticed lost records. Digging into the issue I see the users have started putting quotation marks within quotation marks.  For example: "The Rain in Spain stays mainly in the plains" has become "The rain in Spain stays mainly in the "plains"". What is the best way to handle this? Do I eliminate all quotation marks?
0
Hi,
I have a SQL server DB called DBX_REV where we have a lot of tables and other objects etc. I did create SSIS jobs to do stuff with the tables of that DB.
Now we are transitioning into a new work company. I would definitely want to save all the table definitions and objects and SSIS jobs that I created, within that server? How do I do it?
In other words…. How do I script a copy of the "skeleton" of that entire DB  In such a way so that I can re-create all the objects like tables and etc. within my local desktop  SQL server instance?
Thanks
1
Hello Experts ,

Where can I find template for ETL mapping document ?

Looking for best practices

Are you using Excel or any tools ?

Looking fwd to hear back
0
Hi - I was wondering if someone can advise please. Our organisation uses a ERP systems which has 4000 Relations tables. There is a discussions on creating a master database - where any changes to these tables are populated into Master database. I am worried about this approach - for example, there is no business logic and any changes gets sent to this MD, which is used for integration with other systems? I would have thought you use master database to bring in data from various systems rather than just duplicating one system into another.

The argument for creating a master database is so there is no need for peer to peer integration - I agree peer to peer is not good but thats what what BizTalk is there for. They want to use BizTalk to pull data from the ERP into Master Database and then use SSIS to push data out to other systems. I can't see why they can't they just use BizTalk to transform the data and push the data out. Their other argument if the ERP system ever goes cloud it is not possible to do a full extract (i.e, not delta extract) using SSIS - but the issue nobody even know if we will ever even go hosted solution for the ERP we have. Additionally, even in such a case if there is a good business case I am sure the cloud prvoide will let SSIS access directly to database on the hosted cloud. Do you agree? Can you give your inputs on the flaws with this approach or is it just me worrying?

Thank you.
0
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: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

I have an SSIS package that has a single step to run a PowerShell script.

When I execute the SSIS package via visual studio no issues. But when I run the SSIS package via a job in the SQL instance, and it completes successfully (no errors), no file is outputted as per the script.

Any assistance is welcome, see the attached screenshot for the SQL job configuration.

I am thinking it could be a permissions issue, but I checked the SQL Agent service account and it has permissions to the folder the PowerShell script is writing too.  

Thank you.
Capture.PNG
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 have an SSIS package in which the tasks are:

1. TASK 1: log to a file that the package started.
2. TASK 2: email task
3. TASK 3: EXECUTE SQL TASK
4. TASK 4: email a report

Problem is if the email server gateway is down, then TASK 2 fails.  However, I want TASK 3 to run regardless and I can do this by creating a Precedent Constraint arrow from TASK 1 to TASK 3.    Problem is how would I be notified that the email server is down so that when the email is working again, that I'd know when I can run TASK 4 (a report)?

Thanks.
0
I have bunch of files that I need to copy,but issue is that the file name is same but extensions are something like this .txt0,.txt1,.txt2 etc. So how can I create a SSIS package that could loop through these files and copy these files?
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.