Is there any way where I can check if the field is numeric in derived column/Conditional Split of the data Flow task in Sql Server Integration Services 2005. Functionality should be like ISNUMERIC() function. Currently I'm using ((DT_NUMERIC,12,0)fieldname= (DT_NUMERIC,12,0)fieldname) in the derived column expression to check if th...
I need to fire off an SSIS package when a specific action occurs - what is the best approach in webforms. ...
What is the syntax for reading until the end of file in SSIS VBScript? Dim readFile As FileInfo = New FileInfo(logHourlyName) If readFile.Exists() Then Dim textStream As StreamReader = readFile.OpenText() Dim strLine As String Do While Not EOF <--- what goes here? curLine = textStream.ReadLine() Loop textStream....
I have an XMLA query which returns the State and Last Processed date of an Analysis Services cube as XML, like so: Query: <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DISCOVER_XML_METADATA</RequestType> <Restrictions > <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"> <DatabaseID>S...
For example if I add four (blank) script tasks A1, A2, B1, and B2, where A2 has a constraint to run after A1 and B2 has a constraint to run after B1, but neither A1 nor B1 have constraints, then what order will the tasks run? When I try this it seems to do A1, then B1, then A2, then B2. But why? This question out of curiosity. Edited...
If a file system task such as a rename fails, for example if the file does not exist, then this is considered an error by SSIS. This means the whole package fails. I can get around it by using a script task, or setting the maximum errors for the package to more than one. The problem with setting maximum errors for the package to more tha...
I am trying come up with a way to pull the tables out of an Access database, automate the creation of those same tables in a SQL 2008 DB, and move the data to the new tables. This process will happen on a regular basis and there may be different tables each time. I would like to do this totally in SSIS. C# SQL CLR objects are an optio...
I'm using PSEXEC within batch files to execute DTEXEC (SQL SSIS jobs) as part of a scheduling system. What I'm finding is when a bunch of jobs are triggered together (or even close to one another) I get multiple ERROR 128 messages and the DTEXEC jobs immediately abort. I'm guessing there is some sort of problem running multiple instances...
I have similar code for vb and .net which runs my SSIS package just fine. The problem is the c# code and .net does not run my .dtsx package. I get all sorts of errors in cluding not being able to find the file on my desktop. I tried remotely running the package and locally. Is it my code. I am not sure what to do next. I do have all the ...
I'm working with a set of data from SQL Server that I'd like to get into a group of Excel files. This task needs to be automated to run on a monthly basis. The data looks like Site ID FirstName LastName ------ ------- --------- --------- North 111 Jim Smith North 112 Tim Johnson North 113 Sachin Tedulkar South 201 Horatio...
I have an SSIS Mulitcast object that splits my flow into 2 paths. In the first path I insert the flow into another database. In the second path I update the rows of the flow to show that they were inserted. I need a way to make one path wait until the other path has finished. (So I can handle any insert errors and not update the ...
hi there I just transferred some data from MySql to MsSql (2K5) in a text field, some of my characters, such as apostrophes, are now ? (question mark) to me this indicates some sort of collation or character set error, right? To be honest, I don't know which one should I be using The MySql db currect charset is utf8_general_ci and in ...
Hi all I have an issue executing a package via the API at Microsoft.SqlServer.Dts.Runtime. I have 4 SQL servers that can potentially execute my package in 2 clusters. On the passive nodes in each cluster, execution fails - reported error is that SSIS evaluation period has expired. On the active nodes, it works fine. I don't think it...
I can setup a connection manager that uses the Directory Services OLE provider and points to one of our domain controllers. Then, in the Data Flow area, I create a OLE DB Source and set the Data Access Mode to "SQL Command". I then use this query to pull data from Active Directory: Select employeeid, sAMaccountName From 'LDAP://MyComp...
When an excel data source is used in SSIS, the data types of each individual column are derived from the data in the columns. Is it possible to override this behaviour? Ideally we would like every column delivered from the excel source to be string data type, so that data validation can be performed on the data received from the sour...
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 tr...
Hi, I'm looking for a good book that about Microsoft Business Intelligence Development Studio 2005. Most interest goes to SSIS, but SSAS and SSRS are also important. The book explains in a fair, simple and as much hands on way how the Development Studio for BI works (solution, projects, do's and dont's) and all the transformations in S...
I've got some SQL which performs complex logic on combinations of GL account numbers and cost centers like this: WHEN (@IntGLAcct In ( 882001, 882025, 83000154, 83000155, 83000120, 83000130, 83000140, 83000157, 83000010, 83000159, 83000160, 83000161, 83000162, 83000011, 83000166, 83000168, 83000169, 82504000, 82504003, 8250400...
I have a SSIS package that does a simple read from a flat file and inserts into SQL Server 2005. It was running fine on one computer (computer 1) with both the source and destination pointing locally. The package then got moved to another computer (computer 2), again with everything pointing locally, and started failing. After looking...
I have a task at hand of creating some kind of logic for a database. There is a IBM MQ Series setup in place today that replicates data from a couple of remote systems. At the moment it changes in the remote system and dumps the data into a staging table in a SQL Server 2005 database. I want to validate the data according to some simpl...