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

We have an AIX unix server we need to connect to and then run two commands using C# or something in SSIS. How can we do this? Ultimately we are working to automate what is currently a manual task for us.

cd /strbus/fw
sh job/edid2c
0
OWASP Proactive Controls
LVL 13
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Hi,
I have built a dtsx package using VS 2008 on Windows Server 2008 R2 Standard SP1 and it works fine when I use Preview in one of the OLE DB Source components (see below)
okHowever, I have installed the package in a different environment, VS Pro 2017 on Windows 10 Desktop and I am getting an error when I click Preview, see below.
I have checked the Connection Manager  and it Tests fine, also the same Connection Manager is used in multiple components in the package and it works fine.
Any ideas? If you need any more info please  ask.
Thanks.
error
0
we have migrated our SSIS packages from 2014 to 2016 in SSDT 2017.  I have a data flow task with odedb source connecting to a sql server DB with the below query. the package is running fine with no errors but no data is gettting populated
but if i run this in management studio its giving me 150 rows. Strange thing is if i remove the paramter conditions, its workign fine in SSIS but then IF i hardcode the dates its not populating any data again

do i need to refresh parameters or variables since we did the migration ? can anyone suggest what is causing this issue ?

Many Thanks


select * from vPBTF_Daily_Cash
where Round(Amount,2) <> 0
and Currency = 'GBP'
and (USERDATE = ? or (CD = ? and USERDATE < ?))

The above query is using a datetime variable with expresson (DT_DATE)(DT_DBDATE)GETDATE(). This will basically give todays date . for example 17/10/2019
0
I'm using an ssis pkg to load data from Oracle 12.2.0.1.0 (we just upgraded) into Sql Server 2014.  It worked fine before upgrade, now I'm getting buffer and memory errors.  

Any suggestions?

Connection manager - Native OLE\DB Oracle provider for OLE DB

using a dataflow
0
Can someone give me a example on how to accomplish this task. Does robocopy work or file system task.
0
I have an XML generated out of a stored procedure and stored in a variable in Execute Process task. Scoping at package level.
I am trying to pass that variable on to a power shell script as input to write to an  file with .xml . It executes without any error ,but no file is generated.
Arguments is set through a variable having the following ..

 
   "-ExecutionPolicy ByPass  -command \". '" + @[User::PowerShellScriptFile] + "' " + @[User::XMLFileNamePath] +@[User::XMLOutput]

Open in new window


PowerShellScriptFile --> Power shell file path  
XMLFileNamePath      --> UNC path where file is to be generated

PS file:

   
param ([string]$Filepath , [string]$XMLText)
    
    if (!(Test-Path $Filepath))
    {
       New-Item -path $Filepath -type "file" 
       Add-Content -path $Filepath -value $XMLText
    
       Write-Host "Created new file and text content added"
    }
    else
    { 
      Clear-Content -path $Filepath
      Add-Content -path $Filepath -value $XMLText
    
      Write-Host "File already exists and new text content added"
    }

Open in new window

Please guide me for correct argument value, as I tried with some other options. Following Argument value when run with filepath hard coded in above Ps file ( and removing the params of course) ,does generate file /append the text correctly.  So I guess no permissions issue .

-ExecutionPolicy ByPass -command  ". 'C:\PSCheck\XMLFilegenerate.ps1' Hello"

Open in new window

0
I have a very large table with 1 billion rows
One of the columns stores full address details (street, town, city, country)

Previously it was varchar(max) but since we moved to multilingual we have to change it to nvarchar(max) which doubles the size

Our database size is now growing by 10.5GB a day and I'm looking at ways to reduce this
Is there a way to compress this data and uncompress on the web app?

Open to ideas and suggestions

SQL Server 2017 enterprise
0
ssis 2017 export to excel works in Visual Studio but not in the cloud

Please provide an example of how to do it.

Thanks
0
I’m loading multiple .txt files in Ssis using for each loop, data flow tasks.
First I load the data from a flat file to a staging table, then from staging to target table.
However, I’d like to get the exact number of rows loaded to destination table.I mean, I want the target table count.
Can that be accomplished by using rowcount transformation between source and destination or do I need to get the count after
The destination table is loaded using A SQL statement.Does rowcount give us the rows that were loaded from source to destination
, which can be considered the destination table rowcount?

Could someone help me ASAP.

Thank a million in advance
0
Hi,

I'm using SSIS 2017, SQL SERVER 2016, Azure cloud:

My ssis package gets data from a table and exports it to an existing Excel file successfully.

Problem I'm having with is that in PROD, I can't have the existing Excel file out there, so the SSIS package fails when CONNECTION MANAGER Excel tries to find the destination Excel but can't find it.

Options?

I'm thinking:
1) Create an Excel template in another folder and copy it over to PROD every time the ssis package runs.   But in SSIS TOOLBOX, I don't see any objects for copying a file.
2) Create an Excel template in another folder and copy it over to PROD every time the ssis package runs.   I could create a Powershell vbscript (that copies the Excel template to the PROD folder)  but I don't know if Azure can run the Powershell.
3) any other ideas?  Pleaes provide example code.
4) Can ssis create a new Excel file?
0
CompTIA Cloud+
LVL 13
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Hi, I'm using Visual Studio 2017 to create an SSIS package.  Data isha from SQL Server 2016.

Connection Manager has an object EXCEL.

My package has 2 objects: 1) A dataflow control to get data from SQL Server.   2) Excel Data Flow that points to the Connection Manager object Excel.

I can export data from a SQL SERVER to Excel just fine.

QUESTION: How do I reorder the fields that are exported to Excel?
0
Hi,

I'm using Visual Studio 2017 and Azure SQL Server 2016.  I'm creating an SSIS package and a script in c#.net

QUESTION:
1. My ReadOnlyVariables = System::StartTime
2. In my script Task, how do I get the value of System::StartTime?
3. how do I format datetime to yyyymmdd_hhmm
4. I want to write this information to a LOG file.  If the app1.LOG NOT exist, then create a log file (E.G., DATESTAMP + ' package kickoff'), else append data to it (E.G., datestamp + 'package completed'.
5. My WriteVariables = User:varFolderName.    in my script task, how do I assign a value to to varFolderName ?

What is the code to do this?  Thanks.
0
Hi,

I'm using Visual Studio 2017.

QUESTION:
In Microsoft Azure Enterprise Cloud Services (ECS), I have a variable ENVIRONMENT=DEV\DEV

In SSIS 2017, how do assign a variable varEnvironment equal to the ECS variable ENVIRONMENT=DEV\DEV ?
0
In SSIS 2017, I have a package that has:

1. tab Control Flow has 1 task 'Data Flow'

2. tab 'Data Flow' has 2 objects:
2a. DataFlow component that imports data from SQL SERVER 2016.
2B. FlatFileComponent that exports data to a file.    The Connection Manager has an object named DestinationConnectionFlatFile

ISSUE:
1. DestinationConnectionFlatFile refers to a fixed path such as C:\myfile.txt   but I want it to point to a variable varMyFile whose value is \\myserver\myfile.txt

How do I do that?
0
Adding items to a combo box
0
Hi,

currently, we have tables in SQL server and SSIS jobs created for reporting. With the Azure coming up soon, we will have to get everything to azure, including all tables. SQL server is slated to go away sometime soon
Now I am on the  business side and created a lot of SSIS jobs all this time -

1)how difficult does the transition to Azure looks like?

2)Do I have to recreate all SSIS jobs in Azure and if so how? OR Auzre converts SSIS jobs into Azure equivalent jobs?

3) Is there something similar to "SQL server Data Tools " that I can use in Azure to create small ETLs, data transformation  and data flows etc

thanks
0
Hello,
I have an issue with SSIS toolbox controls in Visual Studio.
I'm using Visual Studio 2019 , I installed Analysis service project  for Visual studio 2019 from Microsoft Marketplace

the problem is when i create any SSIS project and add SSIS Package i cant didn't any control in the SSIS Toolbox
i googled it , but with no luck!

please have a  look at the attached that show the SSIS toolbox with no controls !!
SSIS-Toolbox.png
0
Hi guys,

I am having a really weird issue with sql server deployment. So here is the scenario:

I have a SSIS package which is using a C# user component that reads a file and inserts in a sql server database. When I run package from visual studio, it runs fine, does the insert in sql server which is on a different machine and life is good. Now, I deployed this SSIS package to a sql server and then try to run the SSIS from sql server and I get this error:

OLE DB Destination Input has lineage ID 2000 that was not previously used in the Data Flow task.

OLE DB Command Input has lineage ID 3281 that was not previously used in the Data Flow task.

The buffer type is not valid. Make sure the Pipeline layout and all components pass validation.

INSERT INTO <tablename> failed the pre-execute phase and returned error code 0xC0202020.


Both development machine and Sqlserver machine are 64 bit.

Have been scratching all my hairs out but still can't figure out. Package runs completely fine when I run this in visual studio.

I also copied the C# dlls used by SSIS to the sql machine:

C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents

C:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents

is this a deployment issue? Because Package runs completely fine from visual studio.

I seriously need some quick help.

I really appreciate it guys.
0
SSIS on SQL Server 2016 instance
Web Request Task is being used to upload data to a website.  Current coding works with existing address and key.
The new address and key is failing with a code of 4, indicating lost connection when GetRequestStream. I wrote a standalone VB.NET app with exact code and ran it without errors.  I placed the code below, without the actual address and key of course.  The failure occurs at this section of the code….

 Dim NewStream As Stream = REQUEST.GetRequestStream()
 NewStream.Write(bytes, 0, bytes.Length)
 NewStream.Close()

Private Function SEND_DATA_JSON(ByVal JSON_DATA$, ByVal ADDR$, ByVal KEY$) As String
        Try
            Dim baseAddress As String = ADDR

            Dim REQUEST As HttpWebRequest = CType(WebRequest.Create(New Uri(baseAddress)), HttpWebRequest)

            With REQUEST
                .Headers.Add("Authorization", KEY)
                .Accept = "application/json"
                .ContentType = "application/json"
                .Method = "POST"
                .ContentLength = JSON_DATA.Length
                .Credentials = CredentialCache.DefaultCredentials
                .KeepAlive = False
                .Timeout = -1
            End With

            Dim parsedContent As String = JSON_DATA
            Dim encoding As UTF8Encoding = New UTF8Encoding()
            Dim bytes() As Byte = encoding.GetBytes(parsedContent)

            Dim NewStream As Stream = REQUEST.GetRequestStream()
            

Open in new window

0
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Hi,

I inherited an SSIS 2014 that uses SCD (slowly changing dimension).  

QUESTION: How can I tell what tell when the table was last inserted or updated?  I don't have DBA permissions to run some commands on sys tables that would tell me that information.
0
I'm using an ado.net source to get ad groups. I'm having issues with the queries below.  I cannot use an 'in' or a 'union' to get multiple groups.  Any suggestions?


SELECT displayName, Mail, telephoneNumber, name, sAMAccountName, givenName, sn, distinguishedName
  FROM 'LDAP://xxxx'
WHERE objectClass='user'  AND memberOf in('CN=AGrp-aaaa','CN=AGrp-bbb')



SELECT displayName, Mail, telephoneNumber, name, sAMAccountName, givenName, sn, distinguishedName
  FROM 'LDAP://xxxx'
WHERE objectClass='user'  AND memberOf = 'CN=AGrp-aaaa'
union all
SELECT displayName, Mail, telephoneNumber, name, sAMAccountName, givenName, sn, distinguishedName
  FROM 'LDAP://xxxx'
WHERE objectClass='user'  AND memberOf = 'CN=AGrp-bbbb'
0
I have a source file XML Spredsheet 2003(added sample data in the imageSampleData.jpgXMLSourceReadsData.jpg) , which will be opened in excel when we double click it.
It has multiple namespaces and applying XSLT transformation to remove those extra namespaces and able to read new XMl file generated by XSLT.

But When we read the data from XML source in ssis the data appearing  under one column with all the column names and values (attached image XMLSourceReadsData) , i want to read the data  in tabular format.

Please help me with the solution.

Thanks,
Kishore
0
I have an SSIS package that loads an Excel file to a SQL table. If I just use the file provided by the client, I see a validation error DTC_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. If I open the Excel file and save it (without any changes) then use the saved file as the input, the package runs fine. I am trying to automate this loading process and this is my road block. How can I either (1) adjust the loading process to load from the original file or (2) open and save the file with a script without installing Excel on the SQL server? I do not know the exact process of how the input file is created or the version Excel used as our client is also a recipient of the file.
I have SQL server 2017 (14.0.17224.0) and Visual Studio 2017 SSDT (15.6.6).
Thanks in advance.
0
Hi,

I have three SSIS packages. I need to deploy the packages to different environments dev, sit, qa and production. I am using XML Package Configuration and using package deployment model. It created a manifest with packages and .dtsconfig.

I need to deploy the packages to file system. I cannot use any environment variable, ssis catalog. XML config is having the hard coded path. All the environments are not having the same config path. What to do, its giving the error as its not finding the .dtsconfig file..

Please let me know how to dynamically set the connection string and deploy the package?

thanks
0
I am doing some practice exercises that requires me to connect to an exel spreadsheet. I don't have excel on my computer so i was wondering it there was something I could download that would still allow me to connect to these excel sources?
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.