This is Ravi. I'm working on SSIS 2005 version. I have created the DTSX file from the SQL Server and executed it successfully from my .NET 2005 code.
Now I have a requirement that I need to dynamically change the Source database query. ie., based on the user selection I need to get the data from different tables of SQL and put it into an Excel file.
Hi, I am using SQL Server2005 for SSIS. I want to change the source connection dynamicaly evertime. Let me clear, I have to extract some column from excel to MS-Access. I am using Data Flow Task and able to successfully complete the job. But problem is that, whenever a new file comes , i must have to reconfigure my Excel Source. All the time column in file are same, so no need to worry about mapping but how can my package select a file automatically. I have a directory, suppose "C:dpak". I should able to pick the filename and sheet name from this directory every time when my package will execute.
I have implemented a package to load multiple files to a destination. Since the source was a txt file, i have created as flat file source. However now we are getting files in excel format as well.
Is there anyway the source gets changed dynamically based on the file extension, output of the foreach file enumerator? I can think one solution to have 2 dataflow tasks based on precedence constraining and expression one is for .txt and other one is for .xls.
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview. Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx Here is some of my code..Private Sub ApplyFilter() Dim _filterExpression As String = "" If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then _filterExpression = "CategoryName = '{0}'" End If Me.SqlDataSource1.FilterExpression = _filterExpression End Sub Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged ApplyFilter() End Sub But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
We have some reports that have optional columns. We have them working very nicely, with the column showing or hiding based on values in the report -- works great.
Except -- when the columns are present, the report spans onto two pages, when exported to PDF, in width. That's understandable, as there's a lot of extra data, and exactly what we want. However, when the columns are not present, we get empty pages instead, because the report doesn't automatically contract back onto the size that fits on one page.
Changing the report to a Matrix won't work, as the hidden columns on some of these come as sets of three, where each column in the three has different formatting (different widths, format codes, etc).
I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).
For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.
Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.
My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.
I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.
Hi All, I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example: Source table column Target table column
Name FN_Name Age FN_Age
The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.
Which tasks or approaches can be used to implement this?
Hi. I have this kind of problem since I am not very conversant with SSIS and stripting in VB.NET.
The set-up is the following: Flat File Source -> Script Component -> Flat File Destination
The flat file source looks like this:
NameOfFile Headers Data Data Data and many more rows of Data NameOfAnotherFile Headers Data Data Data and many more rows NameOfAnotherFile Headers Data Data and so on in the same manner...
My stript looks like that (not very complicated): Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim strRow As String = Row.Column0.Trim() Dim strFolder As String = "Data" Dim strConn As String Dim charSep() As Char = {CChar(" ")}
If Mid(strRow.ToLower(), 1, 2) = "d0" Then
' This is the file name, so start a new connection to a new file ' which definitely does not exist yet strConn = strFolder + strRow + ".csv"
' DestinationFile is the name of the ' Flat File Connection Manager With Me.Connections.DestinationFile .ConnectionString = strConn End With
Else Row.Column0 = String.Join(",", strRow.Split(charSep, StringSplitOptions.RemoveEmptyEntries)) End If End Sub
I have several questions regarding this one.
1. Is it ok to change the ConnectionString property of the manager to redirect the row to a different file? If not, what more to do?
2. If the new destination file does not yet exist, will it be created for me or will I get an error? If I do, what to do not to?
3. The most important: What steps to take so that a row is discarded from the pipeline in the script? I want some rows not to be directed to the file destination. These are the lines that contain the name of the file into which the data below belongs.
If you can optimize some steps in the script, then please do so by all means. Thank you for any comments and the knowledge you kindly agree to share with me. Darek
I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error. @TopX int ( or varchar) SELECT @TopX CompanyName, Amount FROM Sales Where..... Why will this not work?
Hello I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display. Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed. For instance in my page load event I would have a CASE statement with numerous SQLString Variables. Here is the current coding for my datasource <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @ProductDescription) ORDER BY PS.Sorter"> <SelectParameters> <asp:QueryStringParameter Name="ProductDescription" QueryStringField="ProductDescription" /> </SelectParameters> </asp:SqlDataSource>I have tried declaring a string variable in my page load event (SQLString) then setting the SelectCommand="SQLString" but this causes a syntax error Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'. Any help would be greatly appreicated!!
I'm novice to SSIS and looking for some help on SSIS dtexec (SQL Server 2005).
Is it possible to change the BulkInsertTableName when running a package via dtexec /SET?
My test scenario contains:
- SQLServer 2005 SP2, servername: SDPM01, instancename: GWLINST1, databasename 1: TEST, databasename 2: DEV, tablename 1: Test_Table1 (both in TEST and DEV database), tablename 2: Test_Table2 (both in TEST and DEV database)
- 1 Data Flow task in BIDS (SSIS)
- 1 Data Flow Source: Flat File Source (Flat File Connection Manager name: FTP File Output + CSV file with a few lines of data that needs to be inserted in a SQL Server table)
- 1 Data Flow Destination: SQL Server Destination (OLE DB Connection Manager name: SDPM01GWLINST1.TEST
I can dynamically change the name of the database via:
I am looking to dynamically change the connection string in my SSIS package, to avoid changing the connection string each time I want to run in different environments.
I have a problem using the table object. I have columns that I sometimes want to show, and I want the page to run off the edge and be two pages. However, usually I'm hiding many of the columns and the table "shrinks" enough to fit on one page. When this happens, the table looks right on the first page, but I get blank pages after it, because the report itself will not shrink.
You may had this problem before and know how to do that or may be can suggest me with some ideas. I am in a process of creating a report wich has 3 groups and the data at the moment grouped by Company - Product - Customer. I want to be able to change grouping order and respectively the data in report , something like Product - Company - Customer or Customer - Product- Company. (just example) Is that possible to do at all? May I add some drag and drop functionality and integrate in my report ?
Hi, say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2.... Does anybody know how can I alter programmatically these two sqldatasources in a gridview? Thanks!!!
I am a relative newbie to SSIS. I have been tasked with writing packages to import data from our clients. We have about 100 clients. Each client has a few different file formats. None of the clients have the same format as each other. We load files from each client each day. Each day the file name changes. I have done all of my current development work with a constant file name in a text file connection manager.
Ultimately we will write a VB application for the computer operator to select the flat file to load and the SSIS package to load it with. I had been planning on accomplishing this thru the SSIS command line interface. Can I specify the flat file to load via a variable that is passed through the command line? Do I need to use a Script Component to take the variable and assign it to the connection manager?
Is there a better way to do this? I have seen glimpses of a VB interface to SSIS. Maybe that is a better way to kick off the packages from a VB app?
I am using Sql Server 2000. I have about 25+ databases . I want to run a series of commands on each database... how can I change the database context - the current database - dynamically in a loop... something like this below:
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)
declare @TABLE_NAME NVarchar(100)
While @@FETCH_STATUS=0
Begin
SET @SQL2 = 'USE ' + @DB_NAME + ';GO;ALTER TABLE ' + @TABLE_NAME + ' MODIFY Name varchar(100)'
PRINT @SQL2
EXECUTE SP_EXECUTESQL @SQL2
End
I am getting an error when i run the above commands: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'GO'
You have a store procedure and inside you make Updates or Inserts against another Server. But that name can change and I dont want to change it manually in everyplace.
Per example:
SELECT * FROM Himalaya.DBName.dbo.tblUserGroup WHERE fldUserID = 7300
I have several Selects, Updates and Inserts pointing to that server.
What I can do if I want to change from Himalaya server to another server?
I would like to modify "Files" attribute of the Foreach Loop of type File Enumerator. This attribute is used to set the mask (for example *.txt) to specify which files to include in the selection. I need to be able to change this mask dynamically depending on package global variable. Is this possible?
I have designed some reports and kept these reports in the report server.
My question is: I want to change report header and footer values dynamically and save the new values in the report template, when the user enters new velues in ASP.NET GUI.
Next time if the user opens the same file the report should display the recent values.
Please help me in passing the parameters from ASP.NET app to SQL Reports and saving the parameters in the report template.
On a 64bit Itanium cluster, I'm having a problem dynamically setting the ConnectionString of a TextFile Source using the "EvaluateAsExpression" = True and setting the ConnectionString using an expression from a variable. I get the error message:
-1073647612,0x,The result of the expression "@[User::vFullFilePath]" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
This is not a problem at all on a 32bit machine. I can run the same package on 32bit and it runs fine.
The funny thing is, I'm able to dynamically change the ConnectionString of a Logfile that we're writing all of the errors to, which I believe is using a TextFile Destination connection under the covers somehow (I'm using the SSIS Logging, to a textfile).
I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?
i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.
Any help would be appreciated
I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched
We are using UnPivot task to convert the columns into rows using the Excel File as source. But the Excel file column names are changing frequenly sometimes its having only 4 columns sometimes its 10 columns.
Everytime we are checking and unchecking the column list in Unpivot Task.
can anybody help us to solve this issue that Unpivot task should take the column name dynamically.
I have a table (source file) where all sourse file(its all text file) name and location stored. i have a dts packeage, through this package all the sourse file will upload. So I need to dynamically change the source file name in dts packages.
Hi Folks ...Question for everyone that I have not been able to figure out. I have an application that is spread across tiers:SQL Connection defined in Web.Config file that connects to SQLServer database.DAL layer created references SQL Connection in Web.Config file - have noticed this copies the connection string information to the local properties for each TableAdapter that is defined in the DAL layer.BLL Layer that references Table Adapters declared in the DAL layer.When the web site is called, the link will provide an encoded id.Sample call to website: http://www.mysamplesite.com/default.aspx?company=AE2837FG28F7B327Based on the encoded id that is passed to the site, I need to switch the connection string to use different databases on the backend.Sample connection string: Data Source=localhost;Initial Catalog=dbSystem_LiveCorp1;User ID=client;Password=live2006 I would need to change Initial Catablog to the following:Data Source=localhost;Initial Catalog=dbSystem_LiveCorp196;User ID=client;Password=live2006How do I do this and have the connection string reflected in all of the Table Adapters that I have created in the DAL layer - currently 100+ Table Adapters have been defined.As we gain new clients, I need to be able to have each clients information located in a different database within SQL Server. Mandated - I have no choice in this requirement. Being as I don't want to have to recreate the DAL for several dozen clients and maintain that whenever I make a change to the DAL to then replicate across multiple copies. There has to be a way to dynamically alter the SQLConnection and have it recognized across all DAL TableAdapters.I'm developing with MS-Visual Studio 2005 - VB. Any help would be greatly appreciated. Thanks ...David Any day above ground is a good day ...
I am developing several charts with column type and sub type as stacked. There is a requirement from the users that they want an option to choose the type of chart.
Is it possible to change chart type dynamically from say Column type to Line type based on user option in front-end?
I need to execute a SQL query, inside a dataflow (not in controlFlow) and need the records returned to continue the dataflow... In my case I cant use lookup and OLE DB COmmand and nothing else...
I need to execute a query and need the records for dataflow... with OLE DB command I cant see the fields returned... :-(
How can I do it? Using a script? Can I use a Script Component? That receive 2 parameters for input and give me the fields returned from query as output?
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
