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,
I am using Visual Studio on my local windows 10 workstation to test an SSIS package which was not created on this machine.
There is a Connection Manager  which connects to an Oracle database as part of the package.
When I click Test Connection I get the error displayed .
ora
Is there some oracle  component I need to install locally?
Any guidance appreciated.
0
OWASP: Threats Fundamentals
LVL 13
OWASP: Threats Fundamentals

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

I have an SSIS package I set up to export csv files from a database.   I can't seem to figure out how to go back and view or edit the query in the .dtsx package.  

Can you view and edit an SSIS package in MSSQL Management Studio?
0
I am creating a SSIS (SSDT) package to automize a process that involves connecting over a SFTP connection. The problem that I am uncertain of is how to pass a RSA token via PSFTP(Putty) session.

Basically what I want to achieve is mentioned in the below link:
https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/

I am uncertain of what the syntax(arguments) should be for passing a RSA token.

Here is the syntax below to manually connect with a batch script;
Example-
open sftp://business:ssdsdsdsds@sftp.web.com/ -hostkey="ssh-rsa 1000 10:ee:23:50:8d:69:28:9c:bd:ba:c3:18:91:fb:42:80"

Thanks
0
Hi I have created a new variable in SSIS with string datatype and used the below expression which is giving me 03042019 but i want it as
03-04-19 which is in dd-mm-yy format. can someone please update the below expression to get my
desired result.

Many Thanks

RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) + (DT_WSTR,4)DATEPART("yyyy",GetDate())
0
Hi, I am running an SSRS report using SSIS  and i have a parameter in SSRS with 4 values.

UK
germany
france
ALL

Now in the ssis script task, i am succesfully able to generate a report for UK, france and germany
seperately but when i try to use ALL , its failing.

below url works fine for UK --

http://ReportServer?%/&rs:Command=Render&rs:Format=CSV&Portfolio=UK

below is what i am using for ALL in my url which is failing

http://ReportServer?%/&rs:Command=Render&rs:Format=CSV&Portfolio=UK&Portfolio=germany
&Portfolio=france

can someone please give let me know the write expression for ALL so that i can get a report for all
3 countries in one report ?

Thanks
0
I have a for each file loop in my SSIS Package that works fine

What I need to do is get a count of files in this folder
C:\EW\FTP\Breckenridge\RollaPoll\Inbound

If > 0 Then proceed with for each
If = 0
Exit process.
0
I have a SSIS package I can run in Visual Studio fine.

I am trying to get it to run automatically in Windows Task Scheduler

The arguments in the window below is this...
xp_cmdshell 'dtexec /f "C:\EW\SSIS\BreckenridgeSSIS_DEV\BreckenridgeSSIS\BreckenridgeSSIS\Package.dtsx"'

But I am getting an "Action failed to start" message in the Scheduler History.

Am I missing something in my arguments?

vs.png
0
The attached file is created by our ERP application. Each file is a record. Each row is a field. The rows are divided by a comma. The text before the comma is the field name. The text after is the data. The ultimate goal is to write the data into an Access table.The field names in the Access table are the same as those in the file. Each file is written to a folder. The folder needs to be monitored so files can be processed as soon as they are created and then archived.  A bar code label program prints a label using the data in the Access table.

My idea was to use a SQL integration Services package to monitor the folder and import the contents of each file to the Access table or to a SQL table and then to the Access table. Is it possible to process the files this way in a timely manner ? If so what would be the steps(detail please) to accomplish this ?

Thanks
MKR26-MAR-19095456_Test_EE.pas
0
Hi all.

I'm working on a SSIS package, I have an "Execute SQL Task" which will email me the results of a sql query in an html table. I'm getting the following error:

"The query failed to parse. The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

I think it has to do with the UNION ALL in the query, I've used the same type of Execute SQL Task for other sql queries that are emailed to me with no issues, but this one is the only one with a UNION ALL. Does anyone have any idea how to fix this?

Begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>Below are the results</H1>' +
    N'<table border="1">' +
    N'<tr><th>Account ID</th>'+
    N'<th>Table Name</th>'+

    CAST( ( SELECT  td=  CRMAccount.AccountID,'' , td='CRMAccount' 
FROM         CRMAccount LEFT OUTER JOIN 
                      [ID Cross Reference].dbo.tblReference ON CRMAccount.AccountID = CAST([ID Cross Reference].dbo.tblReference.Division AS nvarchar(10)) 
                      + N'-' + CAST([ID Cross Reference].dbo.tblReference.ReferenceID AS nvarchar(10))
WHERE     (CAST([ID Cross Reference].dbo.tblReference.Division AS nvarchar(10)) + N'-' + CAST([ID Cross Reference].dbo.tblReference.ReferenceID AS nvarchar(10)) IS NULL)


UNION ALL

  SELECT   td=  CRMAttachment.AccountID,'', td = 

Open in new window

0
In my SSIS for each loop...
It is grabbing each file in the folder and running the processes
Then moving the file just completed into an archive folder
My problem is that it is running the processes nN times depending on number of files remaining

So... if I start with 8 files...
It inserts the first file 8 times and runs the processes that many times
Moves that file
And then does the next file 7 times
And so in

Any ideas?

SSIS
For Each Loop Properties
0
Angular Fundamentals
LVL 13
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

I have a sql table in Sql server 2016 with 2 columns CustomerID and Comment.

CustomerID | Comment
1                         Text1
2                         some big text more than 100 ch
3                         Big comment more than 200 characters


What I would like is to get a result set so that the comment does not exceed 10 characters in 1 row if the comment is more than 10 characters, the comment needs to be split in multiple rows of sets of 10 characters. Following is the expected output.

CustomerID | Comment
1                         Text1
2                         some big t
2                         ext more t
2                         han 100 ch
3                         Big comme
3                         nt more th
3                         an 200 cha
3                         racters


Please assist how can this be accomplished using SQL or SSIS.

Thank you
0
I'm trying to create an expression for a date variable. My SSIS package is calling a particular RESTful JSON API and the date parameters format has to be in the
RFC 3339 or UTC ISO-8601 format. So if I wanted to get a date for today  i'd like to create an expression in order to make it auto populate. I've never ran into this format so i'm not very familiar.

so for today at 4:00PM my variable expression needs to look like this:   2019-02-26T16:00:00Z
0
Hi,
I am trying to use an  sql server 2008 SSIS  Foreach Loop Container to
run the same script against multiple SQL Servers.
felc I have one connection manager and I want to use this to connect to all the servers.
I have a variable set up so that the vb script task (ST current connection manager) can get a new servername on each iteration of the loop.
My problem is how to write the vb script so that it uses the same connection manager but changes the servername.
The code below looks for a Connection Manager with the variable name.
What I need is code to change the server name in the connection manager from the variable...
hope that makes sense.
any guidance appreciated.
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Help, press F1.

	Public Sub Main()
		'
        ' Add your code here
        Dim strConnect As String
        strConnect = "Data Source=" + Dts.Variables("User::ServerName").Value.ToString + ";Provider=SQLNCLI.10.1;Integrated Security=SSPI;Auto Translate=False;"
       'Dts.Connections("OLEDB").ConnectionString = strConnect

		'
		Dts.TaskResult = ScriptResults.Success
	End Sub

End Class

Open in new window

0
Hi,
I have a ssis package that  runs a script against multiple sql servers and saves the results to a table and I am confused as to how one part of it it actually works.
all runs perfectly as shown below
ssis outputThe  first Execute SQL Task truncates the staging table that is to be loaded.
The second Execute SQL Task Gets the list of servers that are to be interrogated and puts the list into a System.Object variable named ServerList.
The Foreach Loop Container is configured to with an Foreach ADO Enumerator pointed at the ServerList variable.
The Foreach Loop variable mappings are set to index 0 for string variable ServerName
The Data Flow Task  contains an OLE DB source which is connected to a Connection manager and contains the following SQL Command Text
 SELECT 
cast(SERVERPROPERTY('MachineName')as sysname) as [Server_Name],
cast (substring(@@version,1,26)as sysname) as [Version],
cast(SERVERPROPERTY('ProductLevel')as sysname)as Service_Pack,
cast(SERVERPROPERTY('ProductVersion')as sysname)as Version_Number,
cast(name as sysname) as Config_Name, 
CAST(value as int) as value_configured, 
CAST(value_in_use as int) as value_in_use,
cast(getdate() as sysname)as Insert_Date
FROM sys.configurations 
WHERE name in( 'Ad Hoc Distributed Queries','clr enabled',
'cross db ownership chaining','Database Mail XPs',
'Ole Automation Procedures','remote access','remote admin connections',
'scan for startup procs','default trace enabled','xp_cmdshell')
order by name

Open in new window

I run the package and everything works fine, the OLE DB Destination table populates with data from 16 different SQL Servers as expected.
MY QUESTION IS…
HOW DOES THE SCRIPT ABOVE GET FIRED AGAINST THE 16 DIFFERENT SERVERS?
Is the connection string/connection manager somehow altered, substituting the ServerList items one by one on each iteration of the Loop?
The package does not contain 16 different Connection Managers.
And if so ,where does this happen? There are no Parameters set in the OLE DB Source.
Any guidance appreciated.
0
I have created a Integration Services app in Visual Studio 2013 that runs on SQL Server under the Integration Services Catalogs.

Its package reads some data with a stored procedure and writes out a flat file - txt.

In order to have access to the server and folder where that output file will go, in the Control Flow section I have added an "Execute Process Task" control.
These are the entries I have for the "Process" for that control:

Executable =  C:\Windows\System32\cmd.exe

Arguments = /C NET USE \\MyServerName\Shared\Download MyPassword /USER:MyUser /PERSISTENT:NO

WorkingDirectory = C:\Windows\System32

I have this package Deployed on the Development server and I can right-click on the package and it runs fine.

However, when I Deploy it to the Production server and then right-click to execute it, I get this error:

In Excecuting "C:\Windows\System32\cmd.exe" "/C NET USE\\MyServerName\Shared\Download MyPassword /USER:MyUser/PERSISTENT:NO" at "C:\Windows\System32", The process exit code was "2" while the expected was "0"

I believe Error Code 2 = The system cannot find the file specified.  I have checked on the Production Server and indeed there is a cmd.exe at location "C:\Windows\System32".

Any ideas what could be different on the Production server that would cause this error?

Thanks,
0
Hi,
I am running the following against a SQL Server 2000 instance and it returns the expected result set
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp
--GO
CREATE TABLE #tmp (name varchar(50),minimum int, maximum int, config_value integer, run_value integer)
--GO
exec sp_configure 'show advanced options', 1
RECONFIGURE
--go
insert into #tmp
exec sp_configure
--go

SELECT 
@@servername as [Server_Name],
substring(@@version,1,26) as [Version],
SERVERPROPERTY('ProductLevel')as Service_Pack,
SERVERPROPERTY('ProductVersion')as Version_Number,
--comment as Config_Name,
name as Config_name,
config_value as Value_Configured,
run_value as value_in_use,
GETdate() as Insert_date
 from #tmp
where name in ('Cross DB Ownership Chaining','remote access','scan for startup procs')

Open in new window

when I put the code into an SSIS 2008 OLE DB Source Data Flow Component and click Preview the results are as expected.
However, when I look at the output of the OLE DB Source using the Data Flow Path Editor, only 5 column names present (name, minimum,maximum,config_value,run_value)
It seems to have dropped ;
@@servername as [Server_Name],
substring(@@version,1,26) as [Version],
SERVERPROPERTY('ProductLevel')as Service_Pack,
SERVERPROPERTY('ProductVersion')as Version_Number,

And ignored the aliases for the others as well as creating  minimum and maximum columns..

Any ideas as to what is going on here?
Any guidance appreciated
0
Hi
I have a data flow task that queries an OLE DB source and inserts the results to an OLE DB destination.
I would now like to run the data task against multiple OLE DB Sources.
The only item that needs to change is the source connection manager.
 I am looking at the FOR EACH Loop container and wondering if I can somehow use this to accomplish the task.
If someone could be guidance on how to approach this I would appreciate it
Thanks
0
Hi there,

In SSIS I need to be able to change some values as the data is imported into the target. I have a series of number codes that I need to change during this process. For example, if the source value is 150, it needs to be changed to 137 on entry into the target.  I have quite a few of these to do so I thought it may be quicker to do a transform rather than run a script. Any assistance would be appreciated.


Thanks
0
I am new to SSIS
I am using a Script component in SSIS for parsing a JSON file.
I am getting errors after I add the C# code in the Script component for parsing of the JSON file.

I am attaching a file which describes the issue that I am facing.
Is this a bug in the Software of Visual Studio 2015 professional? Do I need to re-install Visual Studio 2015 professional?
issues-with-json-scripting.docx
0
Python 3 Fundamentals
LVL 13
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

From an Execute Process task in SSIS I am running a PowerShell script that passes a couple parameters to a .bat file on a remote server that starts a vendor application we use. The process does work as I've tested it with an extremely small test file.

When I execute it with the actual file it takes hours. Whereas if I simply ran the .bat file on the server it takes 15 minutes.

When I check the application server where the .bat file is located, the CPU ranges from 70 to 95% usage and I am the only one on the server. The process taking the CPU is "Java(TM) Platform SE binary".  Is there anything I can do for a work around? We are a healthcare company and everyone has very strict roles and privileges. So I am prohibited to tweaking the server setting (if that's even the problem) w/o opening a help desk ticket. Or perhaps the way I'm trying to execute this just isn't efficient. I've been at this for a few days now. So maybe I'm overlooking something obvious. I see online that this process seems to be a problem for a bunch of folks. I just can't seem to find a solution online. Any assistance is appreciated.
0
I created a SSIS package and executing it in a Batch File. My SSIS package updates an excel spreadsheet but when i run my batch file a pop up window comes up asking me if I want to replace it since the file already exists.  This option doesn't occur when i run it in Visual Studio, Since my SSIS package code saves the file alreaady. how do i get around this with my batch file.

Batch File pop up.
0
I am receiving error: exception has been thrown by the target of an invocation.  I'm trying to execute a macro in an Excel workbook through an SSIS package.  It runs fine when i run it in Visual Studios but when i schedule it in SQL Agent this error comes up. I made sure that my Excel is running 64 BIT.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

public void Main()
		{
            string filename = Dts.Variables["EmailAttachment"].Value.ToString();;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(filename);
            xlApp.Visible = false;
            xlApp.Run("MyMacroName");
            xlWorkBook.Save();
            xlWorkBook.Close(true);
            xlApp.Quit();
            System.Threading.Thread.Sleep(15000);
            Dts.TaskResult = (int)ScriptResults.Success;
        }

Open in new window

0
Whether Drop and Create Stored Procedure is best practice for deployment (or) Alter Stored Procedure is best practice for deployment?

I am working on automating a process of deploying Stored Procedures across the Servers through SSIS Package.

My clarification is, when we deploy an existing stored procedure across the environments , Whether we can drop and create (or) Alter would be fine?
0
Hello Experts, I have almost zero knowledge of batch file syntax. I have a batch file that works. It is executed via a Windows Scheduled task which is called from SSIS. The syntax has date parameters that are currently hard-coded. How can I pass those two parameters to the batch file.

The parameters in the stript below are: -beg-obs 2017-01-01     and -end-obs 2017-12-31

grpr -new-amf-file "E:\Risk Grouper\Output\20190123Medicaid.amd" -patient "E:\Risk Grouper\Extracts\PatientExportMHACO.txt" -patient-format "E:\Risk Grouper\AMF\PATIENT.amf" -medical "E:\Risk Grouper\Extracts\MedicalExportMHACO.txt" -medical-format "E:\RISK Grouper\ACGF\MEDICAL.amf" -pharmacy "E:\RISK Grouper\Extracts\PharmacyExportMHACO.txt" -pharmacy-format "E:\Risk Grouper\AMF\PHARMACY.amf" -RAV US-ALLAGE -all-models -beg-obs 2017-01-01 -end-obs 2017-12-31 -filter-obs -calc-cost-obs

SSIS is running off of one server and the application that the above code is executing is on another server. Both are Windows Server 2012

Any insight is appreciated.
Thank you
0
Looking for best ways to report data from SQL Server Databases to users.   We have tried SSIS packages that outputs data to excel files etc.  
 Report Server-- tedious with so many fields
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.