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

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
Become a Microsoft Certified Solutions Expert
LVL 13
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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
HTML5 and CSS3 Fundamentals
LVL 13
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
I am currently using the following in an SSIS package :
"SELECT *  FROM dbo.TestCategoryTestType WHERE Versionstamp >  " +   @[User::LSERV] + " and Versionstamp <=   "  + @[User::MaxRV]

Open in new window


When I click on the Evaluate Expression button, the above evaluates to:
SELECT *  FROM dbo.TestCategoryTestType WHERE Versionstamp >  0x0000000000000001 and Versionstamp <=   0x0000000000000001

Open in new window



This is what I expect to happen but when I run the package, it fails returning 'Incorrect syntax near '=''

Can anyone see what I'm doing wrong here. Using Visual Studio 2015 to develop.

Many thanks
0
Hello Experts,
I am stuck in a package which was running fine before, but hangs now.  I have about 15 tasks to run in the package.  Task 1 thru 4 runs just fine.  They are just copy the Flat file to local directory, Backup and Delete it etc.  The 5th one is insert the data into the database table where it hangs.

FYI, after 1 thru 4, if I try to execute the 5th task independently, it runs without any problem.  But fails if I run the whole package.

I added an Event Handler, but it does not show me any errors.

Any idea what else could be going on?   Please try to help.

Thank you very much in advance.
0
I have query performance issues when using date parameters in a stored procedure.  The proc takes 15 minutes with hard-coded date values but well over an hour with parameters. I was calling it from SSIS and had trouble passing the variables as datetime2. So I was passing as varchar and handling the conversion in the proc.  Then I took SSIS out of it as i tried to debug. I've been searching for quite some time and I can't get anything to work. This is basically where I've left off.  (SQL2016)

EXEC dbo.GetData @ObsStartDt = '1/1/2017', @ObsEndDt = '12/31/2017'



CREATE PROCEDURE [dbo].[GetData] @ObsStartDt varchar(10) = NULL, @ObsEndDt varchar(10) = NULL
AS

BEGIN TRY  
SET NOCOUNT ON;


declare @StartDT datetime2  
declare @EndDt datetime2

set @StartDT = cast(@ObsStartDt as datetime2)
set @EndDt = cast(@ObsEndDt as datetime2)

select  <stuff)

from HealthPlanDM.MED_CLAIMS_FACT a
join HealthPlanDM.MEMBER_DEMO_DIM b
on a.MEM_DEMO_KEY = b.MEM_DEMO_KEY
join HealthPlanDM.PRODUCT_DIM c
on a.PROD_KEY = c.PROD_KEY      
join HealthPlanDM.DIAGNOSIS_DIM d
on a.DIAG_KEY = d.DIAG_KEY
join HealthPlanDM.PROCEDURE_DIM e
on a.PROC_KEY = e.PROC_KEY
join HealthPlanDM.SERVICING_PROV_DIM f
on a.SERV_PROV_KEY = f.SERV_PROV_KEY
join      HealthPlanDM.POS_DIM g
on a.POS_KEY = g.POS_KEY
join      HealthPlanDM.DRG_DIM h
on a.DRG_KEY = h.DRG_KEY
join HealthPlanDM.TOS_DIMBASE i
on a.TOS_KEY = i.TOS_KEY
join devwork.ACG_PATIENT_EXTRACT1 P                  
on b.MEM_ID = P.patient_id  
left …
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

How to fetch value from a variable in the Process Task in SSIS?

In the Control Flow : - I have  

1 ) Execute SQL Task - > In which i have a select query which fetches 3 row of records. Each mapped to 3 variables.
Variable is mapped to pick the folder path value which is configured in the database.

2) Execute Process Task -> Property ->
                                       Executable ->  I have mapped the physical folder path to it. ( Hard Coded ).

Objective : To get the value of Folder Path from SQL Database and assigned to the variable.

How to map the value to the variable dynamically in  Execute Process Task : SSIS Package?
0
Inside the Forloop each container; In the DataFlow Task, we have Excel Source file. Excel Source file is currently mapped to the source folder path.

We are making the changes to pick the folder path from SQL Table Configuration.  

We have a table name FileConfiguration which has Inbound file path, Archive file path.

How to pick the value from SQL Table and assign to the variables which can be used for passing it to the Excel Source in the SSIS package?
0
In SSIS, How to export an excel file by pointing to the specific tab # 3?

Source file has 3 tabs  (or) 3 sheets.  I need to export specifically sheet # 3 or tab # 3.
Sheet # 3 has few passages in the beginning and then comes table of records.

At present, I manually clean up the sheet # 3 by deleting the passages and having only the table of records.
I load the  Excel by pointing only the sheet # 3 to the SQL Table.
0
I am getting this message when I try and run my SSIS Package in a Command Window

Package runs perfectly fine in debug

I have tried setting the package to 32 bit
And I have downloaded and installed the Access ole dll

Executing this in COmmand Prompt
"C:\Program Files\Microsoft SQL Server\140\DTS\Binn\DTEXEC.exe" /File "C:\EW\SSIS\MyFolder\Package.dtsx"

...blah blah blah...
Error: 2018-12-20 14:34:44.94
   Code: 0xC0209303
   Source: Package Connection manager "Excel Connection Manager 3"
   Description: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".
End Error
Error: 2018-12-20 14:34:44.94
   Code: 0xC020801C
   Source: Send Data to Everyware Get All Events [2]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager 3" failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
0
I have an SSIS project that I created in Visual Studio.  It includes two packages.  One package exports data from SQL tables into an Excel spreadsheet.  The other iimports data from an Excel spreadsheet into a SQL table.  When I run it in Visual Studio on my development machine, both packages work properly.  But when I deploy it to another server as an Integration Services Catalog SSISDB package, the export from SQL to Excel works, but the import from Excel to SQL fails with the following message:

"SSIS Error Code. DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager 'SQLSERVER' failed with error code 0xC0202009."

Then a second error pops up:

"An OLE DB record is available. Source: 'Microsoft SQL Server Native Client 11.0'. Hresult: 0x80004005. Description: 'Named Pipes Provider. Could not open a connection to SQL Server [53]."

Both packages use the same connection manager (called SQLSERVER).  I made sure that the Run64BitRuntime setting is set to False and DelayValidation is set to True.

Again, the export from SQL to Excel runs without error.  It is the import from Excel to SQL that is throwing the errors.

T
0
Hi,
I am using the following to populate an SSIS variable
SELECT ? = CAST(MIN_ACTIVE_ROWVERSION() AS BINARY(8))

Open in new window


When I run this in BIDS it returns a non specific error.
When I run the code in SQL 2005 management studio (minus ? =) it returns the value as expected.

Anyone any ideas on why this is not working?

Any help would be appreciated.

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