I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.
View 9 Repliesthis directory is over 2gb
Program FilesMicrosoft SQL Server100Setup BootstrapUpdate Cache
can you delete the contents safely?
I'm trying to create an import package using BIDS. I'm using SQL Server 2008. The data is saved as a .csv file so that I can use the flat file option for data source. The issue I am having is that when I preview the flat file after selecting it as the datasource, some of the data that have the numeric file format are showing up as non numeric, for instance the value -1,809,575,682,700 is being viewed as ""1 and the package is giving a conversion error.
View 4 Replies View RelatedI've imported an SSIS package into Management Studio (2008 R2) and I've set up a SQL Server Agent job to call the package but it fails due to error code: 0xc00160aa.
As far as I can tell this is because it is unable to read the location of the package despite it being a file system location within Management studio. Also I can run the package manually within Management Studio, but when I try to call it via the job it fails.
I wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
I've been struggling with this issue,
1) Test--FolderName (This Test folder name should use as a database name for below sub folders)
a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql
[Code] .....
The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..
Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?
The databases that they need to run against have the name of the database at the beginning of the name of the folder.
I have table which consists of images. I am trying to export those images to a folder.
declare @path varchar(100)= 'c:images'
SELECT [PICTURE]
,@path+[PICTURE] AS Path
FROM [A].[dbo].[PICTURE]
I am getting this error: The data types varchar and varbinary(max) are incompatible in the add operator.
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
View 9 Replies View RelatedHi,
We have an extensive set of SSIS packages that run daily to build our datawarehouse. We have run into a bit off a maintenance issue which I am hoping someone can help me with. The SSIS packages execute stored procedures. What I would like to know is, is there a quick way to determine what SSIS package is running a certain stored procedure?
My first approach was to load the packages into a table in a database using various xml functions. Basically, the table would contain the package name, and then the stored procedures that package executes. I have run into some difficulties with this though. Has anyone tried anything similar or perhaps know of a tool which could provide similar functionality?
Some help would be much appreciated.
SQL Server 2008 and user connecting with SSIS.How can I trace this user's activity? When I try I do not get the sql they are running. I know their package is pulling data. I assume this is because the code is 'complied' and not really running straight sql type code.
I have looked online and found that there are settings that need to be set in the package for the trace to be able to work as expected.how can I get what the SSIS package is doing if I don't have any control over the package?
Hi all,
I have built a Disaster Recovery Site for my DB.on periodic basis, trn files from production server reach DR server and DR setup will apply them locally.
If the flow is smooth then no issues, if one of the file does not reach DR
the entire setup will halt for the want of the file and I dn't have any means to
know which file is missing.
Both my production and DR site located remotely behind firewalls, i.e i can't
physically access the servers or remotely login to the server.
Can anyone tell me how to see the contents in a folder in the server using
SQL query .
Any help is highly appreciated
Thanks and Regards
Srinivas Varanasi
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
I am having few queries related to follow.
1. Is it possible to call SSIS package from procedure .
2. Also want to supply parameters to procedure.
3. Can multiple users execute that procedure simultaneously.
4. If yes then will it cause any issue if it is run simultaneously by 10 users.
I have created SSIS package to transfer table data(1500 rows tbl size=140 MB).
To transfer data across the network its taking around 1 hr.
I have tried by removing indexes and constraints on destination table.
How should I improve the speed as the table is very small.
Hi,Can anyone tell me how to select the "most recent" date values from agrouped query? Consider the following:CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOTNULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]This is a simplified adjacency list. What I want to do is find the highestvalued item by date for each sub-tree. In other words, the single highestdate item when This.ID_Parent = That.ID_Parent. I think I first need togroup by ID_Parent, then select the TOP 1 from this query, but how toaggregate them so I get the TOP 1 for each ID_Parent?Thanks for any help you can give me,Robin
View 3 Replies View RelatedI have a large table containing about 800 million rows with an average row length of about 1K. The columns in the table are char columns. I need to move the contents of this table into a similar table where the target columns are varchar. The original table column definitions are compatible with the target table but the reverse is not necessarily true. For example, one column is being changed from int to bigint. The table is partitioned.
So, what is the fastest way to migrate the data. I was thinking to unload each partition into a flat file and load the target table running multiple load streams? Is this a good way?
who i can to list the content of all store procedures and funtions in text file ?
View 3 Replies View RelatedI have a ssis package which runs daily. This ssis package has couple of execute sql tasks which load data for yesterday's transaction. Ex.
INSERT INTO Shipped (Div_Code, shipment_value, ship_l_id, shipped_qty, shipped_date, whse_code,
ord_id, ship_id, ship_l_ord_l_id, Created_date) select ord.DIV_CODE as div_code, ship.SHIPMENT_VALUE as shipment_value, ship_l.SHIP_L_ID as ship_l_id, ship_l.SHIPPED_QTY as shipped_qty, ship.SHIPPED_DATE as shipped_date, ship.WHSE_CODE as whse_code, ord.ORD_ID as ord_id, ship.SHIP_ID as ship_id, ship_l.ord_l_id as ship_l_ord_l_id, Getdate() as Created_date from SHIP ship, ORD ord, SHIP_L ship_l where ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))) and ship.WHSE_CODE='WPP' and ord.ORD_ID=ship.ORD_ID and ship.SHIP_ID=ship_l.SHIP_ID
All execute sql task has query like above query. and in some query we have date filter which loads data for yesterday. Ex. one query has ship.SHIPPED_DATE=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). some other query has ord.trans_date=(dateadd(day, -1, CONVERT(VARCHAR(10),GETDATE(),120))). this package runs daily through sql server job, so It loads data for yesterday. Now If i want to run for any particular date, How could we achieve from ssis?
SSIS package meta data information is needed to covert SSIS into a Stored Proc.
100s of table mapping reside within SSIS, what tool/method would get those information so that they copied and pasted to SP to speed up efforts.
when i tried to create an sql server package with ssis Package , i am not able to find the path for putting the ssis package , Whele i browse for package , i am getting olt one SSIS Package forlder and i am not able to locate that forlder in my drive ? what should i do ? How can i copy my package to this folder ?
is there any option to Change the default folder ?
I have two calls to stored procedures that in an SSIS package fails silently. They are simply not executed in production but works fine in test, nothing happens and the sql server agent reports that everything has gone just fine.
In test they have 1 server with db A and B. No issue here.
In prod they have 2 servers with db A and B. On server 1 sql server agent executes a job that includes an SSIS package that on server 2 runs a couple of sp's. That user is db owner on server 2 db B and yet nothing happens. The sp's are not executed.
If I in prod run the job manually then it works, but not when run with the sql server agent account that as said is even db owner.
Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:
use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....
I have a SSIS package running well in production however sometimes the package will fail when the excel file contains more than 25000+ rows.
The SSIS package is run by SQL Server Agent and is set to run in 32bit mode.
I checked the data by loading in batches and all data loaded successfully. But the funny thing is when I run the same package on my local development PC using BIDS and the same data file. The package loads all 25000+ rows successfully.
Is there some setting that is preventing all rows loading in the server environment.
I have created for each container to call all the packages in a folder like below, also created a variable.
Then I add execute package task inside of foreach container and selected file system in a location and in connection called currently creating package name finally in connection properties i added variable in expression which i created and mapped into for each loop container. I referred below link
[URL] ....
All the packages are running but its not ending once all the packages executed its re run and continue the running process, how to stop once all the packages execute.
I have a pretty simple SSIS package that fast loads a 100 million record table into a SQL Server 2008 table on a daily basis. This normally runs fine and completes in about 1 hour. As this is perhaps one of our largest running SSIS packages, about once every 2-3 weeks this SSIS will fail/drop connection. Once it fails, the large number of records will start rolling back. This rollback process can take 1+ hours so I cannot even restart the failed SSIS package immediately. This is a problem.
I am looking for a solution or option so I do not have to wait on that rollback to restart this particular, long running SSIS package. Is there an option/setting to leave the partial data set committed and not rollback? Then I could just restart the SSIS package immediately or set it the SSIS to auto-restart 1 time on failure. The first step in the SSIS does a truncate of the destination table.
I have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.
Hi
I want my package to monitor a directory for a file and when it detects one it will start processing the file. Any help would be appreciated
Regards
Ants
I'm trying to call WinSCP in a SSIS Execute Process Task using a .bat file to automate. SSIS is returning a generic failure error message ("Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "MyServerC$Program Files (x86)WinSCPWinSCP.exe" "-script=MyNASShareWinSCPFTP.bat " at "", The process exit code was "1" while the expected was "0"."). So now I'm trying to run the .bat file by itself. Unfortunately, the command prompt rushes by so fast that I can't see what the server is doing.
I can't find anything on the WinSCP site that indicates how to slow down the .bat file processing or log the remote server responses. I do see how to use the /log switch when using the interactive command line console, but that's not what I want to do.
Hi,
newbie to sql2005. I am trying to write a VB.NET program to list all ssis packages we have stored in a file folder (not in MSDB) where both SqlServer and ssis servers are located. These packages currently running on daily basis by automated jobs with no problem.
I have found the following link on the web that includes VB.Net program. If you scroll down under Example (SSIS Package Store) , you'll see a VB.NET program that I am trying to get it to work for me but keep getting error message like "Cannot find folder......"
http://msdn2.microsoft.com/en-us/library/ms403343.aspx
My goal is to:
1. List all ssis packages stored in the file systems (.dstx)
2. List DB source and distination providers(tables in/out) in a ssis package
3. List properties for DB providers
4. List FlatFile & Excel file provider properties in a ssis package.
My assumption, but not sure, is that I may need to register this file folder (where packages are located) to the SqlServer if that is not done already. Any command or sys table in Sql2005 to figure this out?
I would appreciate any help on this in advance.
Thank You.
I writing the data from sql table to flat file destination. I want to insert the record count in the first line of the destination file.
Record count must preceed 00.EX . Writing 4500 records from database should show 004500 in the first line of flat file.
I have an execute task to store the count in a variable now.
I have a package which is creating a excel file 'ExcelName.xls' and storing in 'E:ReportingDelivered_Reports'. Now I have to attach this report using send mail task and send it to given mail id. To achieve this I have configured the send mail task and in Expression Builder, I have selected the below expression:
"E:ReportingDelivered_ReportsExcelName_"+
((DT_WSTR,4)Year(@[System::StartTime]))+
RIGHT("0"+((DT_WSTR,2)Month(@[System::StartTime])),2)+
RIGHT("0"+((DT_WSTR,2)Day(@[System::StartTime])),2)+".xls"
I need file name should be 'ExcelName_20150601' where suffix is the current date. But I recieve file which name is 'ExcelName', which is the original file name.
I am copying data from database to an excel file through SSIS. database is MS SQL 2005 and BIDS is also 2005.However, the job doing this task fails every time.As per investigation, the result of the query is more than 100,000 rows and we know that excel has a limit of 65000 rows of data.Is there a way of setting the limit up? or something? a better approach maybe so that everything will be copied to the excel file successfully.
The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. End Error Error: 2015-10-22 04:34:58.05 Code: 0xC0047021
Source: Data Flow Task
Description: SSIS Error Code DTS_E_THREADFAILED.
Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:30:00 AM Finished: 4:35:05 AM Elapsed: 304.844 seconds. The package execution failed. The step failed. "
I have a package that need to copy a file from a remote server using path like ipaddresssharedfolder..now, inside of Microsoft data tools, everything runs fine, because y access to those folders on the windows sessions and enter my credentials. however how to I set up the package to use my credentials on the remote server?
Without it, I got error Executed as user: NT ServiceSQLAgent$RETAIL_PRO.and this user does not exists on remote server. so got access denied. error.