.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

Another dynamic SSIS question

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

I'm seeking just a simple yes or now as to whether this can be done, and if so - a gentle nudge as to what approach to take.

The business problem is that our organization has used SQL Server as the data warehouse after a pretty intense ETL process that runs overnight. We recently acquired a Netezza appliance to interfact with Siebel (or Oracle BI), and I'm trying to create a process by which there nightly copies of the DW database in SQL Server over to Netezza.  All of this is simple to manually, but the requirements call me for me to create a metadata table of tables that are to be included in nightly and intra-day hourly updates.  This metadata table has the name of each table, the key data field for gettin recent transactions from Fact tables for hourly updates.

Based on input from our Netezza advisor, Netezza can handle up to six syncronous packages running at at a time.  For each scheduled task, the package would look at the metadata table for a list of tables, and use sys.all_columns to build up the package details.  Fortunately, the name of the tables and fields are identitcal from SQL Server to Netezza, and I've created a .Net page that allows developers to specify new tables they've created and the corresponding key date field that creates the Netezza DDL language to create the table and make data type translations.

View Complete Post

More Related Resource Links

Text Search Strategy Question for the SSIS Gurus...

BACKGROUND: As I have mentioned in some of my other posts I am using SSIS 2005 to replace an existing MS Access 2003 / VBA based ETL engine which I developed some years back.   Part of my existing Access-based ETL performs a text search of the source records and I am now attempting to replicate that functionality in SSIS (replicate in terms of the end-result and not necessarily the methods used to get to that end result).  I have an idea of how I plan to go about this but since am relatively new to SSIS so would greatly appreciate the feedback of those more experienced... DETAILED DESCRIPTION: In the source (Sybase ASE15) database, there is an "object" table (not the actual table name but for illustrative purposes it will suffice).  Within the object table there is a "description" column which is a char(60) datatype.  The description column simply represents a description of the object as defined by the source system end-user. For my ETL solution I allow the ETL administrator to define one or many (1...n) key words or phrases which represent search criteria.  These search criteria are stored in a reference table in my target SQL Server 2005 database (the same database to which my ETL will transform results and store them).  My objective, is as follows:  For each of the 1...n search criteria defined, try and find th

SSIS question

I have a filename variable and I want to load that variable into a SQL table. Can I use a execute SQL task for doing that? If so any ideas as to how it can be done. Thanks.sqldev

SSIS issue with dynamic excel sheet name

Hi Team, I am creating a Excel file dynamically using a script, excute sql and then the dataflow. I am not able to cretae a excel sheet name without an underscore exisitng file: "team data" even though am giving the table name in excute sql task(which is excel sheet name) as "team data" it is creating the name as "team_data" because of which the package is failing? I am able to achieve this with an underscore, as it is a client requirement to generate wihtout an underscore am posting this question. Is this an issue with Sql server 2005? Thanks, Eshwar  

Dynamically Generated Dynamic Menu Items Width Question


I have the following Menu:

<asp:Menu ID="MainMenu" runat="server" Orientation="Horizontal" 
	DynamicVerticalOffset="2" DynamicHorizontalOffset="2" 
	StaticEnableDefaultPopOutImage="false" DynamicEnableDefaultPopOutImage="false" >
        <StaticMenuItemStyle BackColor="#0099cc" ForeColor="#eeeeee" Font-Names="Arial" Font-Size="11" Height="18" HorizontalPadding="10" VerticalPadding="3"  />
	<StaticHoverStyle BackColor="#336699" ForeColor="#ffffff" Font-Names="Arial" Font-Size="11" />
	<DynamicMenuItemStyle BackColor="#0099cc" ForeColor="#eeeeee" Font-Names="Arial" Font-Size="10" Height="18" 
		HorizontalPadding="10" VerticalPadding="3" />
	<DynamicHoverStyle BackColor="#336699" ForeColor="#ffffff" Font-Names="Arial" Font-Size="10" />					
		<asp:MenuItemBinding DataMember="Item" TextField="Title" />
		<asp:MenuItemBinding DataMember="SubItem" TextField="Title" />						

security question about dynamic data


apologies if this has been answered before.

it seems that the scaffolding that generates the list, edit, details apsx pages uses querystrings to pass the primary key for the relevant record. thus is i have a list.aspx showing me a grid of records, the edit hyperlink will be something like http://../tblTable/edit.aspx?ID=n where n is the key of the record to edit.

however, obviously this is not secure for a multi-user site as someone else with a valid login could potentially see records which they shouldnt simply by trying different "ID=n" values?

is there a way to change this behaviour in a Dynamic Data site or will i have to manually code to ensure a user only see records intended for them?

any help is gratefully appreciated



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]

Newbie question about SQl code and SSIS


Hi Guys,

Im coming from a SAP Datawarehouse background and i don't know much about sql queries or statements. I was wondering if i need this knowledge to learn SSIS, and if so in what situations?

Thanks in advance

SSIS Dynamic Connection Provider



I am working on SSIS 2008. My requirement is that I need to connect to SQL Server or Oracle datbase in lookup, but I will get to know this at runtime whether its SQL Server or Oracle. So one option is to create separate connection for each and use separate lookups. But my question is can I use only one connection and one lookup and achieve this? I short can I can change oledb connection provider at runtime?




Clustering SSIS , that SSIS the question !!!


I am pretty much confused as to what to do; According to MS http://msdn.microsoft.com/en-us/library/ms345193.aspx, the disadvantages in making SSIS cluster aware seem to be greater than the advantages; all I want to do is to be able to run my DTS packages every time the cluster fails over to the inactive node; what is the best and safest approach?

This is my current environment:

We are using SQL Server 2005, 64-bit, on a Windows 2003 clustered server with Active/Passive 'A' and 'B' nodes. Databases are on a shared equallogic iscsi SAN.

Any help would be greatly appreciated

SSIS - Passing a date parameter to a SQL Query question again....


I need to pass a date string to my sqlcommand inside of my "datareader source".  I read some of the questions about setting expression variables but I can't seem to figure out how to do it.  I pasted the query below.  The table name contains a date in it so I need to pass that date as a string into my sqlcommand at runtime.  Do I create a variable with the entire SQL command in it or can I just create a single variable that holds the date and just reference that in my datasource reader sqlcommand property?  Can someone assist me with the syntax for creating the expression?

     pages_20101006.`date` AS full_date,
     pages_20101006.`cat` AS category_no,

Thanks very much,



Dynamic Sets / Generate Question



I am stuck on an MDX problem, I wonder if anyone can help with a solution. I have put together something similar to the one I am facing in Adventure Works.

Using the query below I can get the top 10 products by internet sales along with a member showing the sales from the remaining products i.e. those not in the top 10 across all time.


SET TopCustomersSet
    [Measures].[Internet Sales Amount]
MEMBER [Product].[Product].Remainder
AS Aggregate(
        [Product].[Product].[Product].MEMBERS - TopCustomersSet

SET MainSet
AS {TopCustomersSet, [Product].[Product].Remainder}

SELECT [Measures].[Internet Sales Amount] ON 0,
MainSet ON 1

    [Adventure Works]

I can also get the top 10 products for each year using the query below:

SELECT [Measures].[Internet Sales Amount] ON 0,
    [Date].[Fiscal Year].[Fiscal Year].M

SSIS 2008: Problem reading dynamic file during execution



I have a SSIS 2008 project that reads a bunch of files from a file (always the same file) from Machine A.
My package is running on Machine B and this is where the information is being imported to, my Reporting DB (sql 2008) is also located in Machine B. 
I have a dynamic log file that stores the execution errors every time the package is executed and its name is made up of the timestamp of when package starts executing. The "DelayValidation" option for this connection is set to "TRUE". When i execute my Package from Visual Studio 2008, it runs without a problem.
The problem is when i deploy my package into SQL Server 2008 and try running it through DTEXEC. I always get the following error:


Error: 2010-11-03 14:57:29.06
   Code: 0xC001401E
   Source: MyImportPackage Connection manager "ImportLog"
   Description: The file name "\\ip-address\d$\Logs\ImportManager\20101103_145726.txt" specified in the connection was not valid.

SSIS Dynamic Flat Files (Can't get ConnectionString property to dynamically change)


Hi all,

I am trying to do what many have already tried.  I wish SSIS was made more user friendly in this perspective, but I am trying to have SSIS read a folder directory, and get all the files based on a condition that I've specified.

I was following this link:  http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx

So I have a For-Each loop, and within it, I have my Data Flow Task which includes a Flat File Source Connection.  The problem is, how do I pass that information to the connection string?  I can see the connection string in the XML file, but for some reason, I cannot access the property "ConnectionString".  The first property I see when I go to the expressions editor is "DelayValidation."  Furthermore, I've seen documents online where it there is a "connectionString" property for the Flat File Source Connection.  Well, I see no such property.

Any help with specific steps would be greatly appreciated.  Also, once I've figured that part out, I need to be able to move the files to another location after they have been put in the SQL Server DB.  This was much easier in coding a vb.net application.

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

newbie to SSIS - moving data from excel files with dynamic name



I am a new to SSIS but not new to SQL server.  I have been used to programming stored procedures and such to get data in to SQL.  I need to daily import processes of excel files.  However, the name of the excel files change every day.  Is there anyway to dynamically handle this?



Question on Ole db destination provider - SSIS


Hello there

I have a package that imports data from a file to a SQL Server destination. My file has duplicate rows on the primary key column. This will cause the package to fail, since the table will not allow insertion of duplicate primary keys. I use ole db destination task . To this task, i have set error redirection so that any error that causes the rows that can cause error to be redirected. I am facing a different problem due to this property, i have set my ole db destination -- data access as table or view - fast load. If i run the package, all my rows fail insertion into my table and get redirected to my error output file. However if i change my data access mode -- table or view, this package runs and only the duplicate row gets redirected to the error file.

I am not very clear with this scenario. Can anyone please advise?. Since the input data is very high -- more than 5 million rows. The table or view data access mode will make the data insert very slow.

Please correct me if there is anything wrong,.



SSIS 2008 - Question on OLEDB Destination to SQL Server 2000

Simple question, In a SSIS 2008 Package is it possible to have a OLE DB Destination pointing to another Server Machine running on SQL Server 2000 to do bulk insert operation ? . what are the potential pitfalls when while inserting into SQL Server 2000 ?
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