[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Dear Experts,

I am thinking to use SSIS to do the following procedure.

(1) copy many files to Linux machines. then
(2) execute a Linux program (command) to analysis those files. The program will output the result to the local Linux drive
(3) when (2) is done, then pull the result back to Windows machine

For (1) I am thinking of FTP task (by FTP or SFTP)

how about the task of (2) and (3) ?  Could you please help ... Thanks
0
OWASP: Threats Fundamentals
LVL 12
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Using SSIS, I am exectuing a stored procedure and which returns one column which contains values serperated by the | delimiter so the datatype is nvarchar(max).

I can successfully seperate it into columns using the FINDSTRING function, but when I exporting the data to excel (2007-2010 version as an xslx), everything is in the text format.
Excel warns that the cell is a 'number stored as text' on numeric fields.

So I require help on how to ensure numeric fields come out as numbers.
For info the template excel file as headers on the first row that are all text.

Useful info:
Excel Connection String is:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=samplefile.xlsx;Extended Properties="EXCEL 12.0 XML;HDR=YES";

Let me know if further info required, Thanks in advance.
0
Hi all.

We're trying to figure out a way to download emails from an Office 365 email address to a table in our SQL Server 2008 server. Is there a way to do this (ie. SSIS package or other).

We would bring in the from, to, cc, email body

Thank you in advance!
0
Where to begin.
I need to Move some data from Teradata to MS SQL ussing SSIS.

Here is the part I am stuck. I order to create the query in Teradata I need to import a file with 20,000 IDs into a Volatile temp table in TeraData then use that table in a join.

CREATE MULTISET VOLATILE TABLE SomeTempTable(
IDs
)
PRIMARY INDEX(IDs)
ON COMMIT PRESERVE ROWS;

--After i create the temp table I need to do this
INSERT INTO SomeTempTable(?)

Open in new window



In SSIS how do i account for the import here because using SQL Assistant i get a explorer window to point to file with IDs.

I am thinking this would have to be done in a script Task. Even using a script task i am not sure how to point to file?

I would like to know how to get this done. If there is another way not involving script task I am all "EARS" as well.

Thank you in advance!!!
0
Just trying to find out more information about SQL SSIS which I know nothing about.  Can SQL SSIS be used ingests a Multi-Worksheet EXCEL file and separate each worksheet into a separate output file like text or csv?  We are think about using SQL SSIS as a preprocessor for an application that does not support multi-sheet EXCEL files.  I am aware we can use SQL SSIS to do data transformations but now i want to know if this can file re-engineering tasks like this.  Also, SQL SSIS be configured to generate an XML file?
0
Is it possible to create an SSIS package in Visual Studio with the Server Name and Data Source in the Source object as a parameter, so that you can provide the SQL server name at runtime?
0
I run an SSIS package to create a .txt file on to an FTP server and the delimiter I use is a pipe delimeter:

I have to import this file into a third party too and I get this error message: invalid field count

196      196      Invalid field count: expected 17 fields but found 10      [203822][1][903510001][Accessories][331000][0][Uncertified][5000][1/8/8/0/9/18809622][  Edition Red, White & Blue Crystal Minaudiere]
197      197      Invalid field count: expected 17 fields but found 1      [<BR> Condition: 1]
198      198      Invalid field count: expected 17 fields but found 8      [<][][1][][0][0][37][collection]

I can't use any other delimiter because of the nature of the file. Can you provide any suggestions please
0
I have an ADO.Net Connection that goes to a Teradata source. The query gets created on the fly by expressions and SQL TASK before I pass it to the ADO.Net Source but I cant figure out a way to pass my query to the ADO.Net Source.

The only 2 options I get are "Table and View" and SQLCommand
I tried SQLCOMMAND and placed the variable holding query in the input box but it failed.

OLE Source has the variable option to use a variable  for query. I cant user OLE because I dont have the drivers installed for Teradata. How can i get my query on to this ADO.Net source?
0
I have a situation where i need to reproduce a complex SSIS package with varying degrees of differences but for the most part 60% of it is the same across all permutations.

The problem being is each one needs to be recreated from scratch. If any copy and paste is done then it results in METADATA errors when i test in the DEV environment but even more so when i deploy to production.

This quickly turns into a nightmare.

Has any one developed any clever ways to resolve the SSIS METADATA errors that doesn't require either creating each package from scratch  or opening, building, saving, and closing each and every task?

Thank you.
0
Hi,
I have the following VB Script Task in an SSIS package.
It takes a set of variables  (ScriptPath, uName, Pwd, etc) and builds a shell command .
The package is not executing successfully.
I want to somehow be able to output the final text that is presented to the Shell so I can examine it, and possibly try to run it outside of the package.
Any guidance in how I can output this text, possibly a log file? Or output to the screen when running the package on Debug mode in visual studio?
Thanks

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
	Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

	Enum ScriptResults
		Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
		Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
	End Enum
	

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts property. Connections, variables, events,
	' and logging features are available as members of the Dts property as shown in the following examples.
	'
	' To reference a variable, call 

Open in new window

0
C++ 11 Fundamentals
LVL 12
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

I have an SSIS 2017 package that reads an Excel file. I have tried the xls and xlxs versions. The project first checks for file existence before importing. Upgrading to SSDT 2017(VS) from SSDT  2015 got me passed a script version error message. Now I am stuck here. What must I do to get passed this? The project runs just fine in SSDT 2017. It is when it is run a job or package in SQL that it fails. I have installed 64 bit everything. Why is it complaiNIng about 32 bit OLEDB?
SSIS Excel import error
0
I just deployed a job in SQL 2016 just like I have deployed dozens of other jobs. They all run SSIS projects. I received this bizarre message when trying to run the job. The SSIS package runs just fine and I even deployed it a second time. What must I do to correct this? Job Failure
0
When this stored proc executes, it doesn't seem to be able to evaluate whether the variable @SQLAgentJobType contains the text 'Incremental' or 'Manual'.  Right now the value is set in the SSIS environment as 'Manual', but it executed the steps for 'Incremental'. If I reverse the order of the IF -blocks, it will execute the steps for 'Manual' first if the value is set to 'Incremental'.  As an aside, before anyone complains about the start and end dates as being nvarchar(20)-- don't. They are set this way because they are concatenated to another set of SSIS environment variables for an openquery statement needed elsewhere in the SSIS package.  Please review and advise.

ALTER PROCEDURE [etl].[ResetProjectDaterangeVariables]
	-- Add the parameters for the stored procedure here
( @SQLAgentJobType varchar(20),
  @FolderName nvarchar(128),
  @EnvironmentName nvarchar(128),
  @startDate nvarchar(20),
  @endDate nvarchar(20)
)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	 DECLARE
	 -- Note: The string dates are created in order to deal with cobbling together an Informix SQL statement as part of a 
	 --       concatenated text string built by variables used in source dataflows

	 @tempstartDate nvarchar(20)
	,@tempendDate nvarchar(20)
	-- leave room for a leading space
	,@fixedEndTime nvarchar(9)
	,@fixedStartTime nvarchar(9)
	,@var sql_variant;

   -- DEBUG [uncomment]
   --  declare 

Open in new window

0
Hi, I'm using SSIS 2008 and SQL SERVER 2008R2.

I have a table that has several months of data.fep_ssis_upload.JPG
How do I make SSIS prompt the user for a string such as 'JUN 2018' to pass to a stored procedure query or VIEW that gets the data?

Currently, my SSIS calls the table that has data.  Please see the attached 2 printscreens.
Need to get just 1 month of data from this
0
Hi,

I am in the process of migrating a SQL server 2005 to a SQL server 2012.     On the 2005 server,  SSIS packages are used to upload CSV data into various databases.  These SSIS packages are created in Visual studio 2005.

My questions are around the versions of software I require on the new server:

1.  Will the new SQL 2012 server require an install of visual studio? if so, which version?  It would only be used for SSIS development - would an express version be suitable?
2. Are there any migrating tools to help?
0
Can someone point me to a few good links that illustrate how to use SSIS to read an Excel xls file and then insert that data into a SQL table? First I want to check for the existence of the xls file and if it is there proceed with the import. I have started by creating a script task but I cannot see where the connection from that task is made to the FileExists variable I defined so I can determine whether or not to proceed. I have both the script task and the OLE DB Destination task started but I do not get the second arrow indicating failure. I have been scouring the internet for example but have not found any that provide a solution.
0
I have a SSIS Data Flow Task that read Data from OData services, I'm able to connect to it successfully via OData Connection Manager. But when I run the task within SSDT, I'm getting following error

[OData Source [2]] Error: The OData Source was unable to process the data. The remote server returned an error: (401) Unauthorized.


[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on OData Source returned error code 0xC02090F5.  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 posted before this with more information about the failure.

I'm using basic authentication to connect and I can view data in the preview section of OData source editor. I don't know why I'm getting the error, the user has permission to extract the data.
0
Hi,
I’ve made some changes to some SQL Server objects, tables,views, stored procedures, functions etc in my test environment which I now want to move to the production environment.
I want to make backups of the existing objects in production, eg Production_Table_1 as Production_Table_1_backUp.
I’m looking for the easiest way to make a copy of the table in its existing database, and the copy to include all associated indexes,constraints, data etc…
I looked at Transfer SQL Server Object Tasks in SSIS but it doesn’t seem to allow altering the object name.
I’m now looking at using  the “Script Table as” option in Management studio but it doesn’t seem to copy indexes.
Any guidance appreciated.
0
Is it possible to attach a file with the email notification of an SSIS job being completed? I have several SSIS job that run in the morning and produce csv files. For one of the jobs the client not only want email notification of completion but wants the csv file attached to the email. Is this possible? I already have the email notification set up but how do you attach the file?
0
Exploring SQL Server 2016: Fundamentals
LVL 12
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

I was  creating another SSIS  project by copying to another project. The flat file source changed and the target SQL remained the same. I am getting this message"

[Flat File Source [2]] Error: Data conversion failed. The data conversion for column ""MyField"" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

and upon looking at the data I cannot see anything wrong. How do I put in an error trap that will give more detailed information?
0
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 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

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.