EzDevInfo.com

How do I view the SSIS packages in SQL Server Management Studio?

Argh! I created an SSIS package via an Import Wizard and I can't find the SSIS packages on the server using Management Studio. Execute an SSIS package doesn't appear as an option when I go into job scheduler, either.

How to execute an SSIS package from .NET?

I have a SSIS package that eventually I would like to pass parameters too, these parameters will come from a .NET application (VB or C#) so I was curious if anyone knows of how to do this, or better yet a website with helpful hints on how to do it. So basically I want to execute a SSIS package from .NET passing the SSIS package parameters that it can use within it. For instance, the SSIS package will use flat file importing into a SQL db however the Path and name of the file COULD be the parameter that is passed from the .net application. THANKS!

Should programmers use SSIS, and if so, why? [closed]

As a .NET developer, for what reasons should I prefer SSIS packages over writing code? We have a ton of packages in production where I currently work, and they're a nightmare to both "write" (perhaps draw?) and maintain. Each package looks like a bowl of multicolored spaghetti with C# and VB.NET scripts mixed in at the points where the abstractions break down. To figure out what each "Execute SQL Task" or "Foreach Loop" does, I have to double click the damned thing and browse through a tree of literal values and expressions, scattered across multiple tabs.

I'm open minded, so I'd like to know if any other good developers find SSIS more productive than just writing some code. If you do find SSIS more productive, please tell me why.

Using SSIS BIDS with Visual Studio 2012 / 2013

I want to use SSIS /BIDS project in Visual Studio 2012.

I have both Visual Studio 2010 and Visual Studio 2012 installed along with Microsoft SQL Server 2012.

VS 2010 was installed first followed by MS SQL 2012 which got me BIDS integrated with VS 2010.

However I cannot seem to find any help on integrating BIDS with VS 2012.

I came across few question on SO related to this topic but all of them had integration with 2010 and not 2012.

I know that with MS SQL 2012 I get a SQL Server Data Tool. Does this mean that Visual Studio 2012 and BIDS cannot be integrated ?

Solution 2012:

I had to update the solution here since one of the answers I received partially solved the issue. The Answer provided by did solve the issue @user2141936 but I kept on getting an BlockMixedArchitectureInstall error while updating an existing instance.

It worked without with creating a new instance!!

Side Note : Installing the update broken my publishing database project onto 2012 server. You need to download and install a DACFramework patch for it.

Solution 2013:

download and install http://www.microsoft.com/en-us/download/details.aspx?id=42313

How to loop through Excel files and load them into a database using SSIS package?

I need to create an SSIS package for importing data from multiple Excel files into an SQL database. I plan on using nested Foreach Loop containers to achieve this. One Foreach File Enumerator and nested within that, a Foreach ADO.net Schema Rowset Enumerator

Problem to consider: Sheet names are different between excel files but structure remains the same.

I have created an Excel Connection Manager, but the Schema Rowset Enumerator is not accepting the connection manager in the Enumerator configuration.

After researching, I found that you can use the Jet Ole db provider to connect to an excel file. However, I can only specify Microsoft Access Database Files as the data source. Attempting to insert an Excel File as the data source fails

After more research I found that you can use the Odbc Data Provider with a connection string instead of a DSN. After inserting a connection string specifying the Excel file this also failed

I have been told not to use a Script Task to accomplish this and even after trying a last ditch effort to extract data from sheets be accessing the sheets by index I found that the index for the sheets in the different excel files are different

Any help would be greatly appreciated

SQL Server 2012 Integration Services failed when connecting thru SSMS

I had recently installed SQL server 2012 and I used mostly the default settings. Database works fine and I can happily connect using SSMS (SQL Server Management Studio) but when I connect to the Integration Services Server I get this message

Connecting to the Integration Services service on the computer "localhost" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

here is the screenshot

I am not sure why but I am the domain admin and have full rights over the server. Also why when I connect from my Desktop it can successfully connect, only if I connect from the server itself which gives me this issues. How do I fix this so that I can make SSMS on the server connect to its Integration Services instance.

Why doesn't SSIS recognize line feed {LF} row delimiter while importing UTF-8 flat file?

I am trying to import data from a utf-8 encoded flat file into SQL Server 2008 using SSIS. This is what the end of the row data looks like in Notepad++:

I have a couple more images showing what the file connection manager looks like:

You can see that the data shows correctly in the file connection manager preview. When I try to import this data, no rows import. I get an error message indicating that the row delimiter was not found. You can see in the file connection manager images that the header row delimiter and the row delimiter are both set to {LF}. This was sufficient to generate the correct preview, so I am lost to why it did not work to import. I have tried a number of things that have brought zero results:

• Tried using the Wizard import in SSMS...same results
• Tried using data conversion, no impact
• Tried setting the row delimiter to (0a), same results

[Flat File Source [582]] Warning: The end of the data file was reached while reading header rows. Make sure the header row delimiter and the number of header rows to skip are correct.

Thanks for looking at this and I really appreciate any help you can offer.

How to configure SSIS 2012 project to run under different environment configurations?

What would be the best logical way of configuring 2012 SSIS project using the Project Deployment Model?

Consider a scenario of an SSIS Project MyImport-Project having three packages

Project solution:

• MyImport-Project
  • Import-Package-01
  • Import-Package-02
  • Import-Package-03

Project Variables:

• Project-DBConnString : used by all packages to connecting to a single database.
• Project-FolderPath : used by all packages to lookup input files

Environments:

The project gets deployed on

• QA server
• Staging server
• Production server

Is there a standard method of configuring project parameters ?

I saw quite a few articles on Configuration Files for packages. Is there a way to configure Project Parameters depending on the deployment environment ?

What are the differences between Merge Join and Lookup transformations in SSIS?

Hi I'm new to SSIS packages and writing a package and reading up about them at the same time.

I need to convert a DTS into a SSIS package and I need to perform a join on two sources from different databases and was wondering what was the better apporach, to use a lookup or a merge join?

On the surface they seem very similar. The 'Merge Join' requires that the data be sorted before hand whereas the 'Lookup' doesn't require this. Any advice would be very helpful. Thank you.

How do I perform automated unit testing in SSIS packages?

How can I unit test SSIS packages? I want to be able to create and maintain unit tests for various components such as the workflow tasks, data flow tasks, event handlers, etc.

Are there any existing techniques, frameworks, and/or tools that can be used?

SSIS how to set connection string dynamically from a config file

I am using SQL Server Integration Services (SSIS) in SQL Server Business Intelligent Development Studio.

I need to do a task that is as follows. I have to read from a source database and put it into a destination flat file. But at the same time the source database should be configurable.

That means in the OLEDB Connection Manager, the connection string should change dynamically. This connection string should be taken from a configuration/XML/flat file.

I read that I can use variables and expressions to change the connection string dynamically. But how do I read the connection string value from a config/XML/flat file and set the variable?

This part I am unable to do. Is this the right way to achieve this? Can we add web.config files to an SSIS project?

Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column

I am using SQL Server 2008 import and export wizard. I need to import a database. I opened the SQL server import/export wizard and I went through the following actions:-

1. for the destination I chose "SQL server native client 10".

2. then I selected copy data from one or more tables or view.

3. SSIS run immediately

but i got the following errors,

Operation stopped...

• Initializing Data Flow Task (Success)

• Initializing Connections (Success)

• Setting SQL Command (Success)

• Setting Source Connection (Success)

• Setting Destination Connection (Success)

• Validating (Error)

Messages Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ActionID". (SQL Server Import and Export Wizard)

Error 0xc0202045: Data Flow Task 1: Column metadata validation failed. (SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task 1: "component "Destination - AuditActions" (22)" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)

It seems that I can not import identity columns and timestamps columns, so how I can force these values to be imported?

Why aren't my triggers firing during an insert by SSIS?

I have an SSIS data flow task with an OLE DB Destination component that inserts records into a table with a trigger. When I execute a normal INSERT statement against this table, the trigger fires. When I insert records through the SSIS task the trigger does not fire.

How can I get the trigger firing in SSIS?

IsVisualStudio2012ProInstalled() method not found error when running an SSIS package from VS2012

I'm trying to run an SSIS package in Visual Studio 2012. When I click the "Start" button I get this very odd error in a popup from Visual Studio:

Method not found: 'Boolean 
Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled()'. 
(Microsoft.DataTransformationServices.VsIntegration)

Clicking on the show technical information, I get this stack trace:

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

Failed to start project (Microsoft Visual Studio)

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

Method not found: 'Boolean Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled()'. (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)

Has anyone ever seen this error before or know what the issue might be? A bit of googling turned up absolutely nothing for me.

I was able to happily develop and run SSIS packages from Visual Studio 2012 without any issues just a couple days ago (I did reboot my PC over the weekend and some windows updates were installed)

ETA:

I was able to find a couple very recent technet posts about this here and here so I suspect this was something that broke in an update over the weekend. One post says he solved the issue by reinstalling, but of course I'd rather not go that route if I don't have to.

SSIS Excel Import Forcing Incorrect Column Type

I'm trying to import a spreadsheet to our database using SSIS. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. If I try to edit the SSIS package and change the column types in the Excel Source, it won't let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don't match.

Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings? Why does everything from microsoft have to not quite work correctly?

EDIT: I found this: http://support.microsoft.com/kb/236605

I sorted my data so that mixed data types would be at the top, and guess what: The problem reversed. Instead of not importing character data, it stopped importing purely numeric data. Apparently someone doesn't think 12345 can be represented as a string...