.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SSIS - Global Data SOurce - Password issues

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :ASP.Net

I have a simple solution with (right now) 4 packages, 3 configuration files and one "global" datasource.  Here is the View Code on the data source:

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" xsi:type="RelationalDataSource" dwd:design-time-name="02d8345e-665e-4bd8-9323-1e846af220e5" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ID>PPDM37 Staging</ID>
  <Name>PPDM37 Staging</Name>
  <ConnectionString>Provider=SQLNCLI.1;Data Source=engsql03.dev.ihs.com;Persist Security Info=True;Password=;User ID=;Initial Catalog=PPDM37_Staging</ConnectionString>

As you can see, the <ConnectionStringSecurity> node is set with PasswordRemoved.  When editing the data source and checking the Keep Password option on it still saves wit

View Complete Post

More Related Resource Links

VS 2010 and data source issues

I've been banging my head against the wall for the past 4 hours on this one and think I'm going to need a little help here. I must be missing something. I'm in VS 2010 on Windows 7. I've got a web project which has been going fine, but no matter what I do I can't get a data source to show up in the Data Source window. I've added a data source using Data > Add New Data Source, and the "wizard" appeared to complete successfully. I have a data source showing in my solution explorer, and if I open the .xsd file I see all my tables from a remote SQL 2005 database. I have a good data connection to the database server showing up in the Server Explorer window. But no matter what I do I cannot get anything to appear in the Data Sources window, which is greyed out (the commands at the top are grayed out and there's gray text in the middle of the window reading "There are no data sources to show for the selected project"). Under the main Data menu at the top of VS, the "Show Data Sources" and "Add New Data Source" commands are missing. I can sometimes get them to reappear if I restart Visual Studio or reboot the OS, but not consistently. I've been all over Google on this and have tried every suggested solution I can find, to no avail. Anybody have any ideas? Thanks.

Excel Data Source SSIS AcquireConnection failed 0x80004005 unspecified error

Nuances of using Excel data sources with SSIS Your Development Machine Setup Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough. Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will see the title error "Unspecified Error" and "80004005" code. Resolution: Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)". Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.  Live Server (32-bit and 64-bit) Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here  (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en). There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same. You shoul

I need to create a script in SSIS which creates a data source that connects to an Access database.

I need to create a script in SSIS which creates a data source that connects to an Access database. The Access database file name needs to be set as a variable as it will change from month to month. I have no idea what I am doing can anybody give me some tips? Mr Shaw

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

SSIS 2005 - XML Source no data loading to sql server database destination

Hello I am trying to load data from xml source to sql server destination, but the task executes with no data loaded to destination. i really appreciate any guidance in working and troubleshooting xml sources. Regards Harris

How to make up MDX with parameters of SSIS OLEDB data source

Hi everyone,

I want to make up a MDX query by using some parameters, just like:

{...}ON ROWS
FROM [CubeName]

I put this query in the 'sql command' field of OLEDB data source, and replaced the date parts with parameter token '?':

{...}ON ROWS
FROM [CubeName]

However, when I clicked 'parameter' button, the exception below showed:

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable. (Microsoft Visual Studio)
Error Code = 0x80040E51, External Code = 0x00000000:. (Microsoft OLE DB Provider for Analysis Services 2008.)


Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

SSIS: Error in the package when the data in the flat file source is modified


Hi All,

I have a package which loads data from a flat file source to an OLEDB destination, which is executed successfully and data is loaded perfectly.

But later when the data in the flat file source is modified i.e. if an extra column gets added to the text file, the package is throwing an error because it is unable to detect the extra added column.

How can i avoid this error??? I need my package to execute successfully ignoring the changes(added columns) happened in the flat file source.

Please provide me wth your suggestions and solutions....

Thanks in Advance!!

SSIS as data source in SSRS


I have a connection string

="/f c:\test\test2\test2\package.dtsx /set \package.variables[User::varfil].Value;" & Parameters!varempid.Value

but its not returning any results.

SSIS as data source in SSRS connection string


I have a connection string

="/f c:\test\test2\test2\package.dtsx /set \package.variables[User::varfil].Value;" & Parameters!varempid.Value

but its not returning any results. I need a valid connection string.

SSIS Object model - dynamically identifying the source and destination tables of the data flow/ tran


Hello All,

I would like to identifying the source & destination tables of the data flow or the transformation task.

I need to create a custom component which i can put into existing packages which can do some validation.

Please suggest ways i can accomplish this.




SSIS DB2 Stored Procedure as a data source



I have this problem:

In a DTS package I can connect to a DB2 on an AS400 and execute a stored procedure. I can then import this data into a table.


I can't do this in SSIS. DTS uses an ODBC connection. I have tried this with SSIS 2008 r2 – I can use a ado.net connection which picks up the ODBC driver.

I pass in the stored proc as a sql command.

I click preview and all the data is there. I click columns and none are defined.


Has anybody gotten this to work? It seems like a step back to me- DTS did

SSIS excel data source numeric values returned as null

Hello everyone, long time no see!, I have a problem and was hoping someone can help me:
I'm using SSIS 2005 Enterprise edition,  I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server).
When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.

The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it.

I tried the answer posted here: http://devselekta.blogspot.com/2007/09/ssis-excel-data-source-values-returned.html but it only works when the first row of data has a number beginning with zero on this column.
Someone knows how to get the data imported correctly? Thanks in advance.

SSAS Deployment Wizard - Data Source connection string userid and Password getting removed



I have an SSAS database (Analysis Services 2005) which connects to Oracle for the data. I have the userid and password
saved in the connection string.


If I deploy the cube from Visual Studio I DON't have to manually edit the Data Source in the server to input the userid and password. The information is retained.


If I do a build and then deploy .asdatabase file using Deployment Wizard (Microsoft.AnalysisServices.Deployment.exe), the connection information dissappears. I have "Remove Passwords" = False in Project configuration. So when you build the project there will be a file with extension .assecurityinformation which holds the encrypted security info. Somehow

deployment wizard doesn't look at this file.


After the build I execute this command

Microsoft.AnalysisServices.Deployment.exe  .\Project.asdatabase /s:.\Deployment.log

I tried interactively and silent mode. Either way I cannot deploy the project with the userid and password in it.

Any clue?

2008 SSIS and NUMA Memory issues and BLOB Data Types


We're running SSIS 2008, moving data from an Oracle 10g database to a SQL 2008 database.  The SSIS is running on the same machine as the SQL Destination server.  The server has 8 gigs and is windows 2003 sp2

The issue we're having is when our package pulls a blob data type from oracle it will just quit with no errors at about 1.4 million records.   We know there are 6 millions records in the dataset. 

A friend of mine said it was a NUMA Memory issue that it is running out at some point and SSIS thinks the incoming data is finished.  Unfortunately, she said there was no answer. 

I was wondering if someone else had a simliar situation moving blobs from Oracle?


Should I use SSIS or just stored procedure to move data; both source and destination are SQL Server


Please comment the pros and cons of both methods. 


Thanks in advance.

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend